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

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  @JP_PBI  ,
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  @JP_PBI  ,
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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors