cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jrose
Frequent Visitor

%subgroup with switch not working correctly

I'm having trouble with a calculated measure working correctly on all but one column.

Credit where due, the example I used came from the bielite website.  I modified it for use with hierarchical data fields and it worked great.  Thought I would try with non-hierarchical date and it works on all but the one column.

 

Sample data

tblEF

YEAR_FALL STATUS DEG_GRP GENDER NUMBER
2020 FT DEG M 125
2020 PT DEG M 125
2020 FT ND M 25
2020 PT ND M 75
2020 FT DEG F 300
2020 PT DEG F 200
2020 FT ND F 100
2020 PT ND F 50

 

There are two measures

%OfSelection = % of each group in one field of the total of a group selected in another field.

%YearTotal = % of each group in one field, filtered by the group selected in another field, out of the total for the year.

The real data has multiple years so the visuals are several graphs by year, one for each of the categorical columns with slicers based on each categorical column so users can select any combination.

 

When all groups (in all slicers) are selected, the %OfSelection and %YearTotal will be the same.

For example, a visual grouped by Gender will be F = 65%, (650/1000), M = 35% (350/1000) for both %OfSelection and %YearTotal.

A visual grouped by DEG_GRP will be DEG=75% (750/1000) and ND=25% (250/1000) for both.

 

If DEG_GRP "DEG" is selected with a slicer, a visual grouped by DEG_GRP will be DEG = 100% (750/750) for %OfSelection since only that group is selected, and DEG = 75% (750/1000) for %YearTotal.

In this scenario, the Gender visual will show F = 67% (500 females of 750 total DEG) and M = 33% (250 of 750 total DEG) for the %OfSelection.

The %YearTotal will be F = 50% (500 females in DEG of 1000 total for year) and M = 25% (250 males in DEG of 1000 total for year).

 

For some reason, any visual grouped by STATUS does not work.  When everything is selected, this visual should show

FT = 55% (550/1000), PT = 45% (450/1000) for %Of Selection, which is does correctly.  The %YearTotal should be the same since everything is selected.

However, it instead shows 100% for both the FT and PT categories as if ignores the two categories in the column.

 

When using slicers, as an example, if the Gender slicer is used to select F, then the STATUS visual will still show the %OfSelection correctly as FT = 61.5% (400 FT of 650 total females and PT = 38.5% (250 PT of 650 total females).

But the %YearTotal changes to showing total F of the YearTotal, 65% (650/1000), for both FT and PT categories.

I think that's what it is doing.  It's the only comvination in the real data that would give me the incorrect percents being calculated.

It should be FT=40% (400 FT in females of 1000 year total) and PT = 25% (250/1000).

 

It seems to be working correctly with either DEG_GRP or GENDER slicer in any combination.  The actual data set also contains two other columns of differnt categories I left out for simplicity but they all seem to be working correctly.  It is only the STATUS column that is working strangely.  All of columns are just text, I've checked for extra filters, interactions, started the visual from new, swapped the field in a working visual and checked syntax repeatedly.

 

The DAX measure is below, the year is in the actual data which is working at least if all years are selected.  Haven't had a chance to check it thoroughly in trying to figure this out.

 

Still pretty new to PowerBI and DAX and am a complete loss with this one.

Any ideas would be much appreciated.

 

PctOfYrTotal =
VAR EnrollYr =
VALUES ( tblEF[YEAR_FALL] )
VAR ENR_STATUS =
VALUES ( tblEF[STATUS] )
VAR ENR_DEG_GRP =
VALUES ( tblEF[DEG_GRP] )
VAR ENR_GENDER =
VALUES ( tblEF[GENDER] )
VAR YearTotal =
calculate(sum(tblEF[NUMBER]),all(tblEF),tblEF[YEAR_FALL] IN EnrollYr)
VAR GrpTotal =
SWITCH (
TRUE (),
ISINSCOPE ( tblEF[STATUS] ),
CALCULATE (
SUM ( tblEF[NUMBER] ),
ALLSELECTED ( tblEF ),
tblEF[YEAR_FALL] IN EnrollYr,
tblEF[DEG_GRP] IN ENR_DEG_GRP,
tblEF[TYPE] IN ENR_TYPE,
tblEF[GENDER] IN ENR_GENDER,
tblEF[RACE_GRP] IN ENR_RACE_GRP
),
ISINSCOPE ( tblEF[DEG_GRP] ),
CALCULATE (
SUM ( tblEF[NUMBER] ),
ALLSELECTED ( tblEF ),
tblEF[YEAR_FALL] IN EnrollYr,
tblEF[STATUS] IN ENR_STATUS,
tblEF[TYPE] IN ENR_TYPE,
tblEF[GENDER] IN ENR_GENDER,
tblEF[RACE_GRP] IN ENR_RACE_GRP
),
ISINSCOPE ( tblEF[GENDER] ),
CALCULATE (
SUM ( tblEF[NUMBER] ),
ALLSELECTED ( tblEF ),
tblEF[YEAR_FALL] IN EnrollYr,
tblEF[STATUS] IN ENR_STATUS,
tblEF[DEG_GRP] IN ENR_DEG_GRP,
tblEF[TYPE] IN ENR_TYPE,
tblEF[RACE_GRP] IN ENR_RACE_GRP
),
ISINSCOPE ( tblEF[YEAR_FALL] ), CALCULATE ( SUM ( tblEF[NUMBER] ), ALLSELECTED ( tblEF ) )
)
RETURN
IF (
ISINSCOPE ( tblEF[YEAR_FALL] ),
DIVIDE ( GrpTotal, YearTotal ),
1
)

2 REPLIES 2
Icey
Community Support
Community Support

Hi @Jrose ,

 

I think there are no differences among the three conditions:

 

switch.png

 

In addition, I don't understand the scenario you describe very well. Could you create a corresponding .pbix file for me to test?

 

Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

 

 

Best Regards,

Icey

Jrose
Frequent Visitor

They are exactly the same, that's why I posted thinking there had to be something I was missing.

I did catch up with a collegue yesterday and he came to the conclusion it was just an odd glitch where that one field isn't being recognized.

 

We just came up with a quick fix using a caclulated column for now.  I won't post a "solution" since it does seem to be a one-off thing with just a work around unless I can come up with something better.

 

Thanks for taking a look at it though.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.