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
dcormiernj
Helper I
Helper I

Dax Measure works perfect except for row total

I have the following measure that works perfectly except the row totals are not totalling.

The measure is pasted below.  It finds a start date and an end date based on create date field.  then it populates 20% of the value in each of the 5 months.  I pasted an example table below that shows the results I am getting.

FinalAmount =

    SUMX (

      FILTER( 'Project Table',

      EDATE('Project Table'[Create Date],1) <= MAX('calendar'[Date]) &&

      EDATE('Project Table'[Create Date],5) >= MIN('Calendar'[Date])

      ),

      'Project Table'[Value]*.012

        )

The table below is perfect.  each value is correct and the column totals are correct.  but the row total seem to be a copy of the column.  it is not totalling each month for a job total.

any ideas how to get this to total correctly?

 

thank you

 

 AprilMayJunJulAug TotalTotal Should Be
job14004004004004004002000
job25005005005005005002500
job32002002002002002001000
Total1100110011001100110011005500

 

2 ACCEPTED SOLUTIONS
BeaBF
Super User
Super User

@dcormiernj Hi! try with:

 

FinalAmountCorrected =
VAR StartDate = EDATE('Project Table'[Create Date], 1)
VAR EndDate = EDATE('Project Table'[Create Date], 5)
VAR MonthsInScope =
CALCULATETABLE (
VALUES ( 'Calendar'[Date] ),
'Calendar'[Date] >= StartDate && 'Calendar'[Date] <= EndDate
)
VAR NumMonths = COUNTROWS ( MonthsInScope )
RETURN
SUMX (
FILTER (
'Project Table',
EDATE('Project Table'[Create Date], 1) <= MAX ( 'Calendar'[Date] )
&& EDATE('Project Table'[Create Date], 5) >= MIN ( 'Calendar'[Date] )
),
'Project Table'[Value] / 5
)

 

BBF


💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

Community News image 1920X1080.png

View solution in original post

Thank you BBF,  and yes I will try yours to make sure it works.  pasted below are my final measures that do work.  Thank you for all of your help.

FinalAmount Part 1 =

    SUMX (

      FILTER( 'Project Table',

      EDATE('Project Table'[Create Date],1) <= MAX('calendar'[Date]) &&

      EDATE('Project Table'[Create Date],5) >= MIN('Calendar'[Date])

      ),

      'Project Table'[Value] *.012

        )

 

FinalAmount FINAL =  If(HASONEFILTER('Calendar'[Month]),[FinalAmount Part1], SUMX(VALUES('Calendar'[Month]),[FinalAmount Part 1]))

 

View solution in original post

4 REPLIES 4
BeaBF
Super User
Super User

@dcormiernj Hi! try with:

 

FinalAmountCorrected =
VAR StartDate = EDATE('Project Table'[Create Date], 1)
VAR EndDate = EDATE('Project Table'[Create Date], 5)
VAR MonthsInScope =
CALCULATETABLE (
VALUES ( 'Calendar'[Date] ),
'Calendar'[Date] >= StartDate && 'Calendar'[Date] <= EndDate
)
VAR NumMonths = COUNTROWS ( MonthsInScope )
RETURN
SUMX (
FILTER (
'Project Table',
EDATE('Project Table'[Create Date], 1) <= MAX ( 'Calendar'[Date] )
&& EDATE('Project Table'[Create Date], 5) >= MIN ( 'Calendar'[Date] )
),
'Project Table'[Value] / 5
)

 

BBF


💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

Community News image 1920X1080.png

Thank you BeaBF yes this looks like it will work and I will try it.  But just before I saw your response,  I decided to try adding another measure that does a hasonefilter on the month and then do sum on the original measure.  And this worked.  I now get the row totals.  But I like yours too.  Thank you very much.

@dcormiernj Well done! Please try my measure and if it works, accept the answer as solution. At the same time, please paste your working formula, so we can help other users with the same issue!

 

BBF


💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

Community News image 1920X1080.png

Thank you BBF,  and yes I will try yours to make sure it works.  pasted below are my final measures that do work.  Thank you for all of your help.

FinalAmount Part 1 =

    SUMX (

      FILTER( 'Project Table',

      EDATE('Project Table'[Create Date],1) <= MAX('calendar'[Date]) &&

      EDATE('Project Table'[Create Date],5) >= MIN('Calendar'[Date])

      ),

      'Project Table'[Value] *.012

        )

 

FinalAmount FINAL =  If(HASONEFILTER('Calendar'[Month]),[FinalAmount Part1], SUMX(VALUES('Calendar'[Month]),[FinalAmount Part 1]))

 

Helpful resources

Announcements
Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 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