Custom summaries when collapsing row dimensions in Matrix

[bradmitchell]

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

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?

[lkalawski]

Hi @bradmitchell ,

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!

[bradmitchell]

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

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)

The same question would be posed to @richbenmintz solution.

[lkalawski · Accepted solution]

@bradmitchell 

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!

[richbenmintz]

Hi @bradmitchell ,

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])

[bradmitchell]

Thanks @richbenmintz, that looks promising. I'm new to PowerBI, could you direct me to where in the user-interface this conditional logic would be put for the matrix?

[richbenmintz]

Hi @bradmitchell ,

You would have to create a measure then use the measure as a value in the matrix

[richbenmintz]

Hi @bradmitchell ,

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