Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have data (Example
Group Description NumberOf
Group A Description example 1
Group B Description example 1
Group C Description example 1
Group D Description example 1
NA Description example 1
NA Description example 1
Not Known Description example 1
Did not attend Description example 1
Group A Description example 1
Group B Description example 1
Group C Description example 1
And I have a measure
Total of Fact = SUM(Number Of)
And I want a Stacked Bar chart showing just Group A B C and D (So I filter on these groups) and the percentage of each Only against Group A B C and D.
I created the following DAX
Total for Selected Groups = CALCULATE([Total Casualties],FILTER(LocalCasualtySeverity,LocalCasualtySeverity[VehicleGroups] ="Group A"
|| LocalCasualtySeverity[VehicleGroups]="Group B"
|| LocalCasualtySeverity[VehicleGroups]="Group C"
|| LocalCasualtySeverity[VehicleGroups]="Group D"))
And then
% Of Group = [Total for Selected Groups]/[Total of Fact]
By If I add Groups in Group and % Of Group it just comes to 100%
can anyone point me in the right direction. I seem to have a real mental block with DAX for percentages?
Solved! Go to Solution.
Hi Debbie,
You could give this example a try. The ALLSELECTED function is the key here, it applies your calculation on the subset of data in your selection (e.g. if you have 3 categories selected in your visual it will take those categories into account in the sum).
PercOfSubTotal =
DIVIDE (
SUM ( FactSales[Sales Amount] ),
CALCULATE (
SUM ( FactSales[Sales Amount] ),
ALLSELECTED ( DimProduct[Category] )
)
)
Hope that solved the issue in your case as well!
Regards,
Tim
Proud to be a Super User!
Hi @DebbieE ,
Believe that you have two issues, first you are making the division incoretly, so you are dividing the total of cases in ABCD by the cases of each of the group this would give you a calculation of
7 / 2 in the case of group A.
Secondly on your total of selected groups since you are not defining any additional filter context it only considers the axis context so it returns the count of the sames group so you have
2 / 2 = 100%
Redo you measures to:
% of group = DIVIDE( [Total of Fact]; [Total for Selected Groups])
Total for Selected Groups = CALCULATE('Total of fact'[Total of Fact];ALLSELECTED('Total of fact'[Group]))
You don't even need to specify the groups you are selecting since allselected returns the values that you select in the filter if you want to have it defined redo the measure to:
Total for Selected Groups =
CALCULATE (
'Total of fact'[Total of Fact];
ALLSELECTED ( 'Total of fact'[Group] );
'Total of fact'[Group] IN { "Group A"; "Group B"; "Group C"; "Group D" }
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @DebbieE ,
Believe that you have two issues, first you are making the division incoretly, so you are dividing the total of cases in ABCD by the cases of each of the group this would give you a calculation of
7 / 2 in the case of group A.
Secondly on your total of selected groups since you are not defining any additional filter context it only considers the axis context so it returns the count of the sames group so you have
2 / 2 = 100%
Redo you measures to:
% of group = DIVIDE( [Total of Fact]; [Total for Selected Groups])
Total for Selected Groups = CALCULATE('Total of fact'[Total of Fact];ALLSELECTED('Total of fact'[Group]))
You don't even need to specify the groups you are selecting since allselected returns the values that you select in the filter if you want to have it defined redo the measure to:
Total for Selected Groups =
CALCULATE (
'Total of fact'[Total of Fact];
ALLSELECTED ( 'Total of fact'[Group] );
'Total of fact'[Group] IN { "Group A"; "Group B"; "Group C"; "Group D" }
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi Debbie,
You could give this example a try. The ALLSELECTED function is the key here, it applies your calculation on the subset of data in your selection (e.g. if you have 3 categories selected in your visual it will take those categories into account in the sum).
PercOfSubTotal =
DIVIDE (
SUM ( FactSales[Sales Amount] ),
CALCULATE (
SUM ( FactSales[Sales Amount] ),
ALLSELECTED ( DimProduct[Category] )
)
)
Hope that solved the issue in your case as well!
Regards,
Tim
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |