cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jack555
Helper I
Helper I

Stacked Column - Percentage by Column Total

I have a visual like below with % of grand total image 1. However, I would like to have % calculation by column total not by grand total. I tried few solutions suggested by the community, however it ended up with 100% stacked column like second image. In Tableau is out of the box and we get the desired result in couple of clicks.

 

Please help me with the DAX forumula. thank you.

 

Image 1

jack555_0-1670580851239.png

 

Image 2

 

jack555_1-1670581026696.png

 

Desired Outcome

jack555_0-1672295259987.png

 

1 ACCEPTED SOLUTION

Hi @jack555 ,

Base on my research, there is no option or feature to make the stacked column chart display the percentage values while the Y axis display normal value range... As a workaroud, you can put the measure which get the percentage onto Tooltips field, the percentage values will display in tooltips just as below screenshot... Please find the details in the attachment....

yingyinr_0-1672302615574.png

In addition, you can submit a new idea. It is a place for customers provide feedback about Microsoft Office products . What's more, if feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

Best Regards

Community Support Team _ Rena
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

6 REPLIES 6
Mahesh0016
Solution Sage
Solution Sage

Mahesh0016_0-1670587981377.png

Mahesh0016_1-1670588000005.png

Total List Price =

VAR ListPrice = CALCULATE(SUM(TrainingSample2[ListPrice]),ALLEXCEPT(TrainingSample2,TrainingSample2[Business Segment],TrainingSample2[Country]))
VAR RESULT = DIVIDE(ListPrice,[Total ListPrice],0)*100

RETURN
RESULT
Total ListPrice = CALCULATE(SUM(TrainingSample2[ListPrice]),ALL(TrainingSample2[Country]))
 
Hello @jack555 ,
i hope above solutions is helpful.
 
Thank you!

I couldnt get how to do this. could you please simplify it? in this example ListPrice is repeated as column name and VAR name as well. 

Hi @jack555 ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below to get it:

Measure =
VAR _team =
    SELECTEDVALUE ( 'Table'[Team] )
VAR _part =
    SUM ( 'Table'[GT Count] )
VAR _perteam =
    CALCULATE (
        SUM ( 'Table'[GT Count] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Team] = _team )
    )
RETURN
    DIVIDE ( _part, _perteam )

yingyinr_0-1672059297554.png

If the above one can't help you get the desired result, please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for sharing a soultion with real example. However this results in 100% stacked column. But I would like to have the regular stacked column (not 100%) with the labels % totaling to 100 without affecting the height of column. Below is the desired outcome.

jack555_0-1672295179396.png

 

Hi @jack555 ,

Base on my research, there is no option or feature to make the stacked column chart display the percentage values while the Y axis display normal value range... As a workaroud, you can put the measure which get the percentage onto Tooltips field, the percentage values will display in tooltips just as below screenshot... Please find the details in the attachment....

yingyinr_0-1672302615574.png

In addition, you can submit a new idea. It is a place for customers provide feedback about Microsoft Office products . What's more, if feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you. I will go with tooltip since no other workaround. PBI should improve the visuals like Tableau.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.