cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Saket_Casper Regular Visitor
Regular Visitor

Total in matrix visual

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.

 

Capture.PNG

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
 
1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Total in matrix visual

Hi @Saket_Casper 

 

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. 

View solution in original post

4 REPLIES 4
Super User III
Super User III

Re: Total in matrix visual

Hi @Saket_Casper 

 

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. 

View solution in original post

Saket_Casper Regular Visitor
Regular Visitor

Re: Total in matrix visual

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 

Super User III
Super User III

Re: Total in matrix visual

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

 

Saket_Casper Regular Visitor
Regular Visitor

Re: Total in matrix visual

Thank you once again @sturlaws

This was super helpful👍

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors