Possible to create Matrix Visual with stepped layout not displaying subtotals if it is not expanded?

[Anonymous]

I am using the Power BI Matrix Visual to display some data. For every item I want to display I have several subcategories with different attributes.

As I e.g. have dates for each subitem it does not make any sense to display e.g. the earliest start_date and the earliest end_date in the row for the item on the higher level, because it would mix up the different subcategories.

I already deactivated Subtotals > Row Subtotal. This works when I have the whole Matrix expanded and also see the subcategories. But when I have the items not expanded I can also see e.g. the earliest start_date, which I only want to be seen for each subcategory.

Is it somehow - maybe also with another visual possible to display the values only for the subcategories, but not for the superior item?

Any help would be appreciated, thank you!

[Anonymous]

Hi @Anonymous ,

Have difficult to follow, can you show some screenshots to help us better understanding your requirement?

Best Regards,

Jay

[Anonymous]

Hey Jay,

yea sure. I created some sample data.

Because I deactivated "Row subtotals" I can't se any data for the Category (e.g. "item2") if I expand it. This is the way I want it to be. But when I collapse the Categrory (e.g. "item1") I can see the earliest start_date and the earliest end_date and first info. But as it might be that the earliest start_date is in "item1 -1" but the earliest end_date is in "item1 -2" and the first info is in item "item1 -3" this calculation  makes no sense at all.

My goal is to either remove the subtotals even if i collapse the Category, or to have consistent values, e.g. based on the earliest "start_date", but not such a mix, as it is in place right now.

Here you can see my data settings:

Thanks for your help!

[Anonymous]

Hi @Anonymous ,

Thank you for the information.

--"remove the subtotals even if i collapse the Category".

As far as I know this is not supported. It is by design.

Regarding the second situation you mentioned, you may need to create measures to meet the requirement.

Here you could use ISINSCOPE() function to check if the row hierarchy.

https://docs.microsoft.com/en-us/dax/isinscope-function-dax 

Calculate the earliest date according to category and subcategory respectively, and then return the corresponding value through the isinscope() function. Some thing like below:

earliest_start = 
var c_earliest_start = calculate(min([start_date]),allexcept(table,[cat]))
var s_earliest_start = calculate(min([start_date]),allexcept(table,[sub]))
return
IF(isinscope([sub]),[s_earliest_start],[c_earliest_start])

The same as earliest end date.

BTW, there's a trick that could "hide" the subtotal that is to change the font color of subtotal to be consistent with the background color.

Best Regards,

Jay

[Anonymous]

Thanks for the info!

Also found some information on my first question.

Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community also combined with ISINSCOPE function (DAX) - DAX | Microsoft Docs