cancel
Showing results for
Did you mean:
Super User I

## Creating a percentage from a subset of all the data

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?

2 ACCEPTED SOLUTIONS
Solution Specialist

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] )
)
)``````

selection 1

selection 2

Hope that solved the issue in your case as well!

Regards,

Tim

Super User III

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ês

2 REPLIES 2
Super User III

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ês

Solution Specialist

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] )
)
)``````

selection 1

selection 2

Hope that solved the issue in your case as well!

Regards,

Tim

Announcements

#### Microsoft Business Applications Summit sessions

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