topic Re: Dynamic Percentage of Parent Switch Order in DAX Commands and Tips

Dynamic Percentage of Parent Switch Order

dange187 — Thu, 19 Oct 2023 11:04:03 GMT

Hi all,

I am trying to do a percentage of parent calculation which is fine but I am using field parameters so users can dynamically change the row selections in a matrix. If the rows are selected in a specific order then the calculation works fine but if they are selected out of order with the switch calculation then the wrong result is returned. I created a dummy table to represent my issue where there are 3 categories, brand, group and country.

DAX formula is:

"%Share =

VAR _brand = CALCULATE([SUMVALUE],ALLSELECTED('Table'[Brand]))

VAR _country = CALCULATE([SUMVALUE],ALLSELECTED('Table'[Country]))

VAR _group = CALCULATE([SUMVALUE],ALLSELECTED('Table'[Group]))

RETURN

SWITCH(TRUE(),

ISINSCOPE('Table'[Brand]),DIVIDE([SUMVALUE],_brand),

ISINSCOPE('Table'[Group]),DIVIDE([SUMVALUE],_group),

ISINSCOPE('Table'[Country]),DIVIDE([SUMVALUE],_country),

DIVIDE([SUMVALUE],CALCULATE([SUMVALUE],ALLSELECTED('Table')))

If I select country as first level in matrix then group as second level, it works correctly for both levels of the matrix.

If I select group then country the second level is wrong. Presumably the group VAR is passing as true first giving sum of all groups in the selected country.

So my question is, how can I get it to select the correct VAR regardless of the order the field parameters are selected?

Dummy table I created here:

Brand	Group	Country	Value
Brand1	Group1	GB	37
Brand2	Group1	GB	16
Brand3	Group2	GB	16
Brand4	Group2	GB	21
Brand5	Group2	GB	21
Brand6	Group3	GB	47
Brand7	Group3	GB	41
Brand8	Group3	GB	24
Brand9	Group3	GB	25
Brand1	Group1	FR	21
Brand2	Group1	FR	49
Brand3	Group2	FR	41
Brand4	Group2	FR	22
Brand5	Group2	FR	19
Brand6	Group3	FR	37
Brand7	Group3	FR	27
Brand8	Group3	FR	16
Brand9	Group3	FR	37
Brand1	Group1	DE	9
Brand2	Group1	DE	31
Brand3	Group2	DE	17
Brand4	Group2	DE	21
Brand5	Group2	DE	50
Brand6	Group3	DE	49
Brand7	Group3	DE	33
Brand8	Group3	DE	34
Brand9	Group3	DE	40

Re: Dynamic Percentage of Parent Switch Order

lbendlin — Sat, 21 Oct 2023 21:02:21 GMT

Fascinating issue. Your problem is that the SWITCH() statement exits once it finds a match, and you have to hard code the order it checks in.

In this scenario [Group] loses out because it comes last.

if you put it up front then it works again

I am not aware of a way to sense the order in which the field parameters have been selected by the user. It can be shown visually through the Parameter table but -to my knowledge - not probed programmatically.

So you would need to overcome two large hurdles - first, know the order in which parameters were selected, and then, modify the switch statement dynamically.

Power BI Desktop and Service are missing the EVALUATE function, sadly.

EDIT: This might be helpful: Using SELECTEDVALUE with Fields Parameters in Power BI - SQLBI

EDIT 2: No, it's not. It only provides which fields are selected but not the order they were selected in.

Re: Dynamic Percentage of Parent Switch Order

dange187 — Mon, 23 Oct 2023 08:21:57 GMT

Thank you for your effort in looking into this. I think the problem requires too much effort to solve for the benefit. I've reverted to a simpler version that does not give a sum to 100% of the sub-category but always to 100% of the overall total.

Re: Dynamic Percentage of Parent Switch Order

lbendlin — Wed, 25 Oct 2023 15:43:32 GMT

I don't think you should mark my post as the solution (as there isn't currently one). I'll see if I can come up with a workaround of sorts. The problem is also that you can add field parameters multiple times to a visual. That would then make the "order of picking" even more ambiguous. Fun stuff!

Re: Dynamic Percentage of Parent Switch Order

lbendlin — Thu, 26 Oct 2023 21:27:58 GMT

Alberto confirmed that this is not currently possible.

Re: Dynamic Percentage of Parent Switch Order

RamanaiahM — Tue, 05 Nov 2024 19:15:42 GMT

**bleep**! I have encountered the same scenario. when i found this post , i hurriedly scrolled down thinking some good soul would have already provided solution 😀.

My post for help: Same problem as the one in this post, but more complicated.

https://community.fabric.microsoft.com/t5/forums/recentpostspage/post-type/message/category-id/powerbi/user-id/837751

Re: Dynamic Percentage of Parent Switch Order

lbendlin — Tue, 05 Nov 2024 20:42:23 GMT

Well, times have changed. Not a problem any more with Visual Calculations