Wrong Total with DAX Measure

Question

&nbsp;I have a DAX Measure to calculate the total revenue for each week. I am not getting the correct total from the measure.I checked the weekly values and they are correct but the total is not correct.I also put the same measure in a card visual still the total is wrong.&nbsp;&nbsp;Total =
VAR _thisyear =
    YEAR ( [Today] )
VAR _currentweek =
    WEEKNUM ( [Today] ) 
VAR _minweek =
    CALCULATE (
        SUMMARIZE (
            FactRevenue,
            "Min Date lY", CALCULATE ( MIN ( FactRevenue[weeknum] ) )
        ),
        FILTER ( DimCalendar, DimCalendar[CalendarYear] = _thisyear - 1 )
    )
VAR _total =
    SUMX (
        SUMMARIZE (
            FactRevenue,
            FactRevenue[CalendarWeekKey],
            "Revenue YTD", CALCULATE (
                SUM ( FactRevenue[Revenue] ),
                FILTER (
                    DimCalendar,
                    DimCalendar[CalendarWeekNumber] &lt;= _currentweek
                ),
                FILTER ( DimCalendar, DimCalendar[CalendarYear] = _thisyear ),
                FILTER (
                    DimCalendar,
                    DimCalendar[CalendarWeekNumber] &gt;= _minweek
                ),
                GROUPBY ( Branch, Branch[Branch] )
            )
        ),
        [Revenue YTD]
    )
RETURN
    IF ( ISBLANK ( _total ), BLANK (), _total )&nbsp;&nbsp;&nbsp;

jirkaz · Answer

db1Intermtn&nbsp;It's because the total doesn't sum the individual values but applies the same logic as your measure to the entire context you're in.&nbsp;I'm just curious why you have such a complex logic since the measure is not a running total.&nbsp;You should be just fine with a proper calendar table and a simple SUM measure.&nbsp;Then you'd just put the weeknum field and the measure in your visual and you should be fine.

db1intermtn · Answer

simple SUM measure doesn't work for me in this case because here I am calculating the YTD values for each branch but there is a condition that YTD should be calculated from the same week since when the data is available for that branch from last year.

If branch A has data from week 3 of 2019, my measure calculates YTD for that branch for 2020 from week 3 and not from week 1.

Any way I can get the correct total here with my measure?

greg_deckler · Answer

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
&nbsp;
Can't be more specific than that without sample data to test with.&nbsp;Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

v-juanli-msft · Answer

Hi&nbsp;db1Intermtn&nbsp;
Would you like the result below?

&nbsp;
Best RegardsMaggieCommunity Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft · Answer

Hi&nbsp;db1Intermtn&nbsp;
Is this problem sloved?&nbsp;
If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
If not, please let me know.
&nbsp;
Best Regards
Maggie

Forum Discussion

Wrong Total with DAX Measure

5 Replies

Related content

Total measures show wrong total when report is published

Wrong Total

% of total measure

Wrong Total in Table

Total of Measure