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
DebbieE
Community Champion
Community Champion

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 Sage
Solution Sage

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





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

Proud to be a Super User!




View solution in original post

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

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
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" }
)

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



timg
Solution Sage
Solution Sage

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





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

Proud to be a Super User!




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.