Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I'm attempting to create a variable Outcome based on the result of 2 variables; deprivation and % attendance at AreaCode level.
In my data model there is a staff table that records their area code of residence (town) and their main pet in the household and another table for attendances (staff either office based or not). A '1 to many' relationship was created using staff_ID enabling grouping number of staff into each area code - the area code has a fixed deprivation value between 1 to 10.
Both variables are further grouped into classes; deprivation values 1 to 3 = C, 4-7 = B, 8-10 = A and % attendance <33% = 3, 33-66% = 2, 67%+ = 1.
Outcome will have a value of either A1-3, B1-3 or C1-3.
DAX used {Outcome = CONCATENATE('Summarize_table'[Band_deprivation], 'Summarize_table'[Band%attendance])}
Table below shows a sample of one AreaCode which for each line (by Pet) produces the correct Outcome however I'm unable to produce Outcome for the overall AreaCode which should be B2 (since 57.6% attendance is between 33%-66%) - see ???
The reason this has produced 3 lines for AreaCode E0001 is because within the area of residence has also grouped up each pet owned. Pet is a key field as I need to create a multi select slicer for Pet which should dynamically generate the overall %attendance which then leads to recalculation of Outcome value. The report will be illustrated using shape maps with bi-variate correlation.
AreaCode | AreaName | Band_deprivation | Deprivation | Pet | count_of_staff | attendance | %attendance | Outcome |
E0001 | Biggins | B | 4 | Cat | 275 | 213 | 77.5% | B1 |
E0001 | Biggins | B | 4 | Dog | 440 | 251 | 57.0% | B2 |
E0001 | Biggins | B | 4 | Parrot | 178 | 50 | 28.1% | B3 |
893 | 514 | 57.6% | ??? |
DAX used in Summarize_table -
Summarize_table =
FILTER
(
SUMMARIZECOLUMNS
(
REF_Wards[AreaCode],
REF_Wards[AreaName],
Staff[Pet],
Staff[Deprivation],
Staff[Band_deprivation],
"count of attendance",[Count_of_attendance],
"count of staff",[Count_of_staff],
"% uptake", DIVIDE([Count_of_attendance],[Count_of_staff],0)
),
REF_Wards[AreaCode] <> "" &&
Staff[Pet] IN
{
"Cat",
"Dog",
"Parrot",
"Pig",
"Chicken",
"Snake",
"Monkey",
"Lizard",
"Spider",
"Guinea Pig"
}
)
Thanks for reading. Your suggestions are greatly appreciated.
Solved! Go to Solution.
Hi @Anonymous ,
Whether your [Outcome] uses a calculated column. If the calculated column is of Text type, the result of the last row will not appear. You can change it to Measure.
Here are the steps you can follow:
1. Create measure.
%attendance =
DIVIDE(SUM('Table'[attendance]),SUM('Table'[count_of_staff]))
Outcome =
SWITCH(
TRUE(),
[%attendance]<=0.33,MAX('Table'[Band_deprivation])&""&3,
[%attendance]>0.33&& [%attendance]<=0.66,MAX('Table'[Band_deprivation])&""&2,
[%attendance]>0.66,MAX('Table'[Band_deprivation])&""&1)
%totalattendance =
DIVIDE(SUMX(ALL('Table'),'Table'[attendance]),SUMX(ALL('Table'),[count_of_staff]))
Result:
[Outcome] After changing to measure, B2 appears in the last line
2. Use [Pet] as the slicer, place [%attendance], [%totalattendance] in the Values of the Clustered column chart, and compare the results
3. Result.
If my answer does not meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Whether your [Outcome] uses a calculated column. If the calculated column is of Text type, the result of the last row will not appear. You can change it to Measure.
Here are the steps you can follow:
1. Create measure.
%attendance =
DIVIDE(SUM('Table'[attendance]),SUM('Table'[count_of_staff]))
Outcome =
SWITCH(
TRUE(),
[%attendance]<=0.33,MAX('Table'[Band_deprivation])&""&3,
[%attendance]>0.33&& [%attendance]<=0.66,MAX('Table'[Band_deprivation])&""&2,
[%attendance]>0.66,MAX('Table'[Band_deprivation])&""&1)
%totalattendance =
DIVIDE(SUMX(ALL('Table'),'Table'[attendance]),SUMX(ALL('Table'),[count_of_staff]))
Result:
[Outcome] After changing to measure, B2 appears in the last line
2. Use [Pet] as the slicer, place [%attendance], [%totalattendance] in the Values of the Clustered column chart, and compare the results
3. Result.
If my answer does not meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |