Forum Discussion

jack555's avatar
jack555
Advocate III
3 years ago
Solved

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

 

Image 2

 

 

Desired Outcome

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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....

    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

6 Replies

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago

    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!
    • jack555's avatar
      jack555
      Advocate III
      3 years ago

      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. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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 )

        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