Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Total not adding up the row values in a Matrix visual

I have a matrix visual where I need to show the Products and:
1. one column with the quantity of sold Products
2. one column with the the most recent (before the selected Year & Month) quantity produced of the same products 
The problem I face is the Total on Produced QTY shows the total quantity of all products which were produced in the selected period instead of keeping the filter context of those products which have sales data only ... Row values are correct but the sum of column is wrong. This is because In the Grand Total the report include the produced quantities for Products E and F which don't have any sales data. Any idea ?

 

The slicers are: YEAR and MONTH

 

I first calculate which month was last time a Product from my context (where Sold QTY <>0) was produced: 

 

Most Recent Production Date
VAR SlicerDate = DATE(SELECTEDVALUE(dimCalendar[Year]), SELECTEDVALUE(dimCalendar[Month]),1)
VAR MostRecentDate = CALCUALTE(MAX(Production[Date]);FILTER(ALL(dimCalendar); dimCalendar[Date] < SlicerDate))

 

Here I calculate the Produced QTY within the first month backwords where the sold product was produced:

 

Produced QTY

VAR LastDay = EOMONTH(Most Recent Production Date,0)
VAR FirstDay = DATE(YEAR([Most Recent Production Date]), MONTH([Most Recent Production Date]),1)

VAR myResult = CALCULATE (

                                 SUMX(Production; quantity);

                                 FILTER(ALL(dimCalendar[Date]); dimCalendar[Date] >= FirstDay && dimCalendar[Date] <= LastDay)
                   )

RETURN
          IF(ISBLANK([Sold QTY], BLANK(), myResult)

 

The expected result is for Grand Total to get 7000 instead of 8300.

 

 

Thanks a lot!

 

  • Hi Anonymous ,

     

    You ALL function is ignoring all the filter contexts and that's why total is not getting changed on slicer value changes.

    Try replacing ALL with ALLSELECTED and the totals should probably change. ALLSELECTED will take the slicer filtering into consideration.

     

    Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

2 Replies

  • Hi Anonymous ,

     

    You ALL function is ignoring all the filter contexts and that's why total is not getting changed on slicer value changes.

    Try replacing ALL with ALLSELECTED and the totals should probably change. ALLSELECTED will take the slicer filtering into consideration.

     

    Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

  • Anonymous , Try like

    SUMX(Production[product]; IF(ISBLANK([Sold QTY]), BLANK(), myResult))

     

    Also try allselected, in place of all