Forum Discussion
Custom summaries when collapsing row dimensions in Matrix
Let's say I have a matrix, which has 3 dimensions in the form of rows. There's a single column called weather. Thus, the cells are filled with weather {sunny, cloudy, rainy}.
Rows Dimensions:
- Month
- Week
- Day
- Week
When I collapse Week, I would like it to show a custom summary. Currently the only summarize options seem to be First, Last, Count, and Count (Distinctive). Is there a way to further customize summaries? For example if I wanted (TOTAL_SUNNY / ALL DAYS) which might be (15/30)
For example, When I collapse the Week dimension, it may show summaries like:
- Week1 [2 sunny 5 rainy]
- Week2 [7 cloudy]
- Week3 [2 rainy 5 sunny]
or even more simply aggregated towards sunny days out of the total:
- Week1 (2/7)
- Week2 (0/7)
- Week3 (5/7)
I don't see any options to get that specific. Is this possible?
Anonymous
I have prepared an example solution. Please check the file.
You can use this measure to check the levels as well as the action assignments:
Selected Date Level = SWITCH ( TRUE (), ISINSCOPE ( 'Table'[Date].[Day] ), MAX ( 'Table'[Weather] ), ISINSCOPE ( 'Table'[Date].[Month] ), VAR __SunnyDays = COUNTROWS ( FILTER ( 'Table', 'Table'[Weather] = "Sunny" ) ) RETURN IF ( ISBLANK ( __SunnyDays ), 0, __SunnyDays ) & "/" & COUNTROWS ( 'Table' ), ISINSCOPE ( 'Table'[Date].[Quarter] ), VAR __SunnyDays = COUNTROWS ( FILTER ( 'Table', 'Table'[Weather] = "Sunny" ) ) RETURN IF ( ISBLANK ( __SunnyDays ), 0, __SunnyDays ) & "/" & COUNTROWS ( 'Table' ), ISINSCOPE ( 'Table'[Date].[Year] ), VAR __SunnyDays = COUNTROWS ( FILTER ( 'Table', 'Table'[Weather] = "Sunny" ) ) RETURN IF ( ISBLANK ( __SunnyDays ), 0, __SunnyDays ) & "/" & COUNTROWS ( 'Table' ), BLANK () )Proud to be a Super User.
If I helped, please accept the solution
and give kudos!
7 Replies
Hi Anonymous ,
I would suggest that you create 3 Measures
Sunny = Calculate(COUNTROWS('Table'), 'Table'[weather]="sunny") Cloudy= Calculate(COUNTROWS('Table'), 'Table'[weather]="cloudy") Rainy = Calculate(COUNTROWS('Table'), 'Table'[weather]="rainy")Then use these measures in your visual to display the count of weather events
Hi Anonymous ,
If I understand it right, there is no other option except creating a measure that will show a summary for the appropriate hierarchy level.
As you mentioned, only these four options are default.Please send me some sample data or a .pbix file and I will try to help you.
Proud to be a Super User.
If I helped, please accept the solution and give kudos!
Once creating a new measure, is it possible to modify the summary to use that measure, but keep the individual cells using a different measure? I would suppose that's not possible.
For example:
- Month1
- Week1 (2/7)
- Day1 Sunny
- Day2 Sunny
- Day3 Raining
- Day4 Raining
- Day5 Raining
- Day6 Raining
- Day7 Raining
- Week1 (2/7)
In this example the expanded cells are using the weather measure, but the summary collapsed view might be using a different, summary-type, measure. I assume that's not possible?
Because if not, I would fear this happening:
- Month1
- Week1 (2/7)
- Day1 (1/1)
- Day2 (1/1)
- Day3 (0/1)
- Day4 (0/1)
- Day5 (0/1)
- Day6 (0/1)
- Day7 (0/1)
- Week1 (2/7)
The same question would be posed to richbenmintz solution.
Hi Anonymous ,
You can control which measure is used at which level of the Hierarchy by using conditional logic like
conditional measure = SWITCH(TRUE(), HASONEVALUE(table(Level3)), [measure3]), HASONEVALUE(table(Level2)), [measure2]), HASONEVALUE(table(Level1)), [measure1]), [measure1])
- Month1
