cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DebbieE
Super User I
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
timg
Solution Specialist
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 1selection 1

selection 2selection 2

Hope that solved the issue in your case as well!

 

Regards,

Tim

View solution in original post

MFelix
Super User III
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" }
)

MFelix_0-1612519038785.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User III
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" }
)

MFelix_0-1612519038785.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

timg
Solution Specialist
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 1selection 1

selection 2selection 2

Hope that solved the issue in your case as well!

 

Regards,

Tim

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!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors