Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Aslam_mh
Frequent Visitor

DAX Quarterly total calculation problem

please help me why this happening

when i do DAX for calculating Q1 and Q2 same formula shows errors display on grid
Q1 Revenues =
CALCULATE(
SUM('newincomestatement'[Value]), 'newincomestatement'[Type] = "Revenues", 'newincomestatement'[Quarter] = "Q1")
Q2 Revenues =
CALCULATE(
SUM('newincomestatement'[Value]), 'newincomestatement'[Type] = "Revenues", 'newincomestatement'[Quarter] = "Q2")


when i do DAX for calculating Q1 and Q2 different formula shows result proper

Q1 Revenues =
CALCULATE(
SUM('newincomestatement'[Value]), 'newincomestatement'[Type] = "Revenues", 'newincomestatement'[Quarter] = "Q1")

Q2 Revenues =
CALCULATE(
SUM('newincomestatement'[Value]),
FILTER(
'newincomestatement',
'newincomestatement'[Type] = "Revenues" &&
'newincomestatement'[Quarter] = "Q2"
)
)

just simple table :

CategoryItemsMonthsValueTypeQueter
RevenueWholesale01-01-20202228540.6RevenuesQ1
RevenueDistributor01-01-20201586017.3RevenuesQ1
RevenueExport01-01-2020552810.3RevenuesQ1
RevenueWholesale01-02-20202161815.3RevenuesQ1
RevenueDistributor01-02-20201574191.8RevenuesQ1
RevenueExport01-02-2020555590.8RevenuesQ1
RevenueWholesale01-03-20202199248.2RevenuesQ1
RevenueDistributor01-03-20201862057.3RevenuesQ1
RevenueExport01-03-2020891669.5RevenuesQ1
RevenueWholesale01-04-20203089731.8RevenuesQ2
RevenueDistributor01-04-20201809582.9RevenuesQ2
RevenueExport01-04-2020783203.2RevenuesQ2
RevenueWholesale01-05-20202531816.1RevenuesQ2
RevenueDistributor01-05-20201297408.1RevenuesQ2
RevenueExport01-05-2020718983.7RevenuesQ2
RevenueWholesale01-06-20202488748.5RevenuesQ2
RevenueDistributor01-06-20201151066.7RevenuesQ2
RevenueExport01-06-20201003144.1RevenuesQ2
1 REPLY 1
Zsolt_Kralik
Frequent Visitor

Hello,
The CALCULATE function is modifying the filter context. Using your first code:


Q1 Revenues =
CALCULATE (
SUM ( 'newincomestatement'[Value] ),
'newincomestatement'[Type] = "Revenues",
'newincomestatement'[Quarter] = "Q1"
)

DAX would return the first quarter values even for Q2 as CALCULATE would overwrite the filter context.

Using FILTER would mean that you only iterate through that subset of data and for Q1 in this case you would return blank.

Q2Revenues =
CALCULATE (
SUM ( 'newincomestatement'[Value] ),
FILTER (
'newincomestatement',
'newincomestatement'[Type] = "Revenues"
&& 'newincomestatement'[Quarter] = "Q2"
)
)

NOT using a DAX function (eg. an explicit measure) only an implicit measure would yield the correct values as well and you can use a filter on the visuals or a slicer.


Zsolt_Kralik_0-1735762612390.png

PS. Use daxformatter.com to have nicely formatted DAX.
Hope this helps.

Helpful resources

Announcements
MarchFBCvideo - carousel

Fabric Monthly Update - March 2025

Check out the March 2025 Fabric update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

"); $(".slidesjs-pagination" ).prependTo(".pagination_sec"); $(".slidesjs-pagination" ).append("
"); $(".slidesjs-play.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-stop.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-pagination" ).append(""); $(".slidesjs-pagination" ).append(""); } catch(e){ } /* End: This code is added by iTalent as part of iTrack COMPL-455 */ $(".slidesjs-previous.slidesjs-navigation").attr('tabindex', '0'); $(".slidesjs-next.slidesjs-navigation").attr('tabindex', '0'); /* start: This code is added by iTalent as part of iTrack 1859082 */ $('.slidesjs-play.slidesjs-navigation').attr('id','playtitle'); $('.slidesjs-stop.slidesjs-navigation').attr('id','stoptitle'); $('.slidesjs-play.slidesjs-navigation').attr('role','tab'); $('.slidesjs-stop.slidesjs-navigation').attr('role','tab'); $('.slidesjs-play.slidesjs-navigation').attr('aria-describedby','tip1'); $('.slidesjs-stop.slidesjs-navigation').attr('aria-describedby','tip2'); /* End: This code is added by iTalent as part of iTrack 1859082 */ }); $(document).ready(function() { if($("#slides .item").length < 2 ) { /* Fixing Single Slide click issue (commented following code)*/ // $(".item").css("left","0px"); $(".item.slidesjs-slide").attr('style', 'left:0px !important'); $(".slidesjs-stop.slidesjs-navigation").trigger('click'); $(".slidesjs-previous").css("display", "none"); $(".slidesjs-next").css("display", "none"); } var items_length = $(".item.slidesjs-slide").length; $(".slidesjs-pagination-item > button").attr("aria-setsize",items_length); $(".slidesjs-next, .slidesjs-pagination-item button").attr("tabindex","-1"); $(".slidesjs-pagination-item button").attr("role", "tab"); $(".slidesjs-previous").attr("tabindex","-1"); $(".slidesjs-next").attr("aria-hidden","true"); $(".slidesjs-previous").attr("aria-hidden","true"); $(".slidesjs-next").attr("aria-label","Next"); $(".slidesjs-previous").attr("aria-label","Previous"); //$(".slidesjs-stop.slidesjs-navigation").attr("role","button"); //$(".slidesjs-play.slidesjs-navigation").attr("role","button"); $(".slidesjs-pagination").attr("role","tablist").attr("aria-busy","true"); $("li.slidesjs-pagination-item").attr("role","list"); $(".item.slidesjs-slide").attr("tabindex","-1"); $(".item.slidesjs-slide").attr("aria-label","item"); /*$(".slidesjs-stop.slidesjs-navigation").on('click', function() { var itemNumber = parseInt($('.slidesjs-pagination-item > a.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); });*/ $(".slidesjs-stop.slidesjs-navigation, .slidesjs-pagination-item > button").on('click keydown', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); var itemNumber = parseInt($('.slidesjs-pagination-item > button.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); }); $(".slidesjs-play.slidesjs-navigation").on('click', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); }); $(".slidesjs-pagination-item button").keyup(function(e){ var keyCode = e.keyCode || e.which; if (keyCode == 9) { e.preventDefault(); $(".slidesjs-stop.slidesjs-navigation").trigger('click').blur(); $("button.active").focus(); } }); $(".slidesjs-play").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-stop").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-play")) { $(".slidesjs-stop").focus(); } } }); $(".slidesjs-stop").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-play").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-stop")) { $(".slidesjs-play").focus(); } } }); $(".slidesjs-pagination-item").keydown(function(e){ switch (e.which){ case 37: //left arrow key $(".slidesjs-previous.slidesjs-navigation").trigger('click'); e.preventDefault(); break; case 39: //right arrow key $(".slidesjs-next.slidesjs-navigation").trigger('click'); e.preventDefault(); break; default: return; } $(".slidesjs-pagination-item button.active").focus(); }); }); // Start This code is added by iTalent as part of iTrack 1859082 $(document).ready(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); $("#tip2").attr("aria-hidden","true").addClass("hidden"); $(".slidesjs-stop.slidesjs-navigation, .slidesjs-play.slidesjs-navigation").attr('title', ''); $("a#playtitle").focus(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").mouseover(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").blur(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#playtitle").mouseleave(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#play").keydown(function(ev){ if (ev.which ==27) { $("#tip1").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); $("a#stoptitle").focus(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").mouseover(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").blur(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").mouseleave(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").keydown(function(ev){ if (ev.which ==27) { $("#tip2").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); }); // End This code is added by iTalent as part of iTrack 1859082
Top Kudoed Authors