Total in matrix visual

[Anonymous]

Hi there!

I have created a matrix visual (attach below) in power BI on a historical dataset with country and division as columns and corresponding measures like sales, sales MTD, sales QTD, sales last year, Sales MTD last year and so on. The matrix visual is linked with a date slicer.

the issue I am facing is that the total under row total is not matching to the total of corresponding column values. The reason being that the data is for multiple countries (2 countries) and multiple division(4 divisions), but on that particular date, data is available only for 1 country(A) and 3 division whereas the total at the end of the matrix for historical measures (QTD, YTD, last year QTD, last year MTD and Last year YTD) is showing the sum of both countries and all-division where ever sales data is present historically. 

is there a way to achieve one of the following:

        1. Sum of only the values been shown in the matrix

        2. Combination of all countries & division irrespective of the date slicer

the measures I have calculated are similar to this one:

Sales Fiscal YTD = CALCULATE(sum('Data'[Sales (Daily)]),DATESYTD('Date'[Date]))

Regards

[Anonymous]

Hi there!

I have created a matrix visual (attach below) in power BI on a historical dataset with country and division as columns and corresponding measures like sales, sales MTD, sales QTD, sales last year, Sales MTD last year and so on. The matrix visual is linked with a date slicer.

the issue I am facing is that the total under row total is not matching to the total of corresponding column values. The reason being that the data is for multiple countries (2 countries) and multiple division(4 divisions), but on that particular date, data is available only for 1 country(A) and 3 division whereas the total at the end of the matrix for historical measures (QTD, YTD, last year QTD, last year MTD and Last year YTD) is showing the sum of both countries and all-division where ever sales data is present historically. 

is there a way to achieve one of the following:

        1. Sum of only the values been shown in the matrix

        2. Combination of all countries & division irrespective of the date slicer

the measures I have calculated are similar to this one:

Sales Fiscal YTD = CALCULATE(sum('Data'[Sales (Daily)]),DATESYTD('Date'[Date]))

Regards

[sturlaws · Accepted solution]

Hi @Anonymous 

you can create a measure to use as a filter:

filterMeasure =
COUNTROWS ( SUMMARIZE ( 'Table'; 'Table'[Country]; 'Table'[Division] ) )

Add this measure to the filter pane of your visual and set it to filter on greater or equal to 1

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too. 

[Anonymous]

Hi @sturlaws ,

Thank you very much for this. It's working.

Could you please also explain the rationale behind this? It will help me in understanding DAX better 😀 

Thanks in advance.

Regards,
Saket 

[sturlaws]

I'll try 🙂

Summarize('Table';'Table'[Country];'Table'[Division]) returns a table with the distinct rows of Country and Division that exist in the current filter context. 

In your table visual the filter context is made up of what you put on the rows of the matrix(division and country) and the slicer with date/month.

When you choose a month where some divisions and countries don't have a value, the measure returns 0(or blank). When you add that measure to the filter pane, the countries/divisions which have no values in the current context, these are filtered out from the year to date-calculation

[Anonymous]

Thank you once again @sturlaws. 

This was super helpful👍