Totals not working on Matrix

[Anonymous]

Hi,

Measure is not working at totals level. Created a new Measure which calculates the Prior year Sales Amount WTD, On the Power BI report there are 3 scliers to select Year, Week and Status. 

On the Matix report I need show Channel ,Distrinct ,Store Number ,Status,SalesAmt , SalesAmtPy and status can change over time So when calcualting PY I need to ignore the Status from Prior Year and show only the current year Status with SalesAmt and SalesAmtPy on one line.To acheive this I used the above Calc which seems to be working fine but the totals and subtotals in matrix report or totals in tabular report are not correct. Only at the lowest level in matrix they are calculating fine. on the higher level it is not .

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, 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

If you still have the problem, please share a simple sample pbix file and expected output.

Best Regards,

Lin

[Anonymous]

Here's the Dax code, sorry but for some reason it is not letting me add the code to original post;

SalesAmtPY:=
VAR DateRange =
    FILTER (
        ALL ( 'DimDate' ),
        'DimDate'[AdjustedYearNumber]
            = VALUES ( 'DimDate'[AdjustedYearNumber] ) - 1
            && CONTAINS (
                VALUES ( 'DimDate'[AdjustedWeekofYearNumber] ),
                'DimDate'[AdjustedWeekofYearNumber], 'DimDate'[AdjustedWeekofYearNumber]
            )
            && CONTAINS (
                VALUES ( 'DimDate'[AdjustedWeekofYearandDayofWeekNumber] ),
                'DimDate'[AdjustedWeekofYearandDayofWeekNumber], 'DimDate'[AdjustedWeekofYearandDayofWeekNumber]
            )
    )

     
RETURN
    IF (
        ISBLANK ( [SalesAmt] ),
        BLANK (),
        CALCULATE ( 'FactSales'[SalesAmt], DateRange, ALL ( DimStatus[StatusDescription],DimStatus[Status] ) )
    )

Let me know if any need additional information is needed.

[Anonymous]

I have Measure to Calc Prior Year Sales, if the current year selected week sales are blank I don't want to display the PY Sales for the store. On the day level Dax calc is working fine if you see on the attached screenshot Tuesday doesn't have data but on Store level the sum 82795 also includes the tuesday Prior Year Sales. How to exclude that ? Here my Dax Measure I'm working on;

[Anonymous]

Measure is not showing right Totals , 

SalesAmtPY:=
VAR DateRange =
    FILTER (
        ALL ( 'DimDate' ),
        'DimDate'[AdjustedYearNumber]
            = VALUES ( 'DimDate'[AdjustedYearNumber] ) - 1
            && CONTAINS (
                VALUES ( 'DimDate'[AdjustedWeekofYearNumber] ),
                'DimDate'[AdjustedWeekofYearNumber], 'DimDate'[AdjustedWeekofYearNumber]
            )
            && CONTAINS (
                VALUES ( 'DimDate'[AdjustedWeekofYearandDayofWeekNumber] ),
                'DimDate'[AdjustedWeekofYearandDayofWeekNumber], 'DimDate'[AdjustedWeekofYearandDayofWeekNumber]
            )
    )

     
RETURN
    IF (
        ISBLANK ( [SalesAmt] ),
        BLANK (),
        CALCULATE ( 'FactSales'[SalesAmt], DateRange, ALL ( DimStatus[StatusDescription],DimStatus[Status] ) )
    )