Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to aggregate data in nested subtotal

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.

 

AreaCodeAreaNameBand_deprivationDeprivationPetcount_of_staffattendance%attendanceOutcome
E0001BigginsB4Cat27521377.5%B1
E0001BigginsB4Dog44025157.0%B2
E0001BigginsB4Parrot1785028.1%B3
     89351457.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.

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

v-yangliu-msft_0-1621486041799.png

[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

v-yangliu-msft_1-1621486041802.png

3. Result.

v-yangliu-msft_2-1621486041805.png

 

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.

View solution in original post

1 REPLY 1
v-yangliu-msft
Community Support
Community Support

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:

v-yangliu-msft_0-1621486041799.png

[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

v-yangliu-msft_1-1621486041802.png

3. Result.

v-yangliu-msft_2-1621486041805.png

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.