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
)
