cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lit2018pbi Frequent Visitor
Frequent Visitor

Display percentage in stacked column chart

Hi,

 

I wanted to display percent of the overall set for the year as a tooltip in Power BI for a stacked column chart. Is there anyway to do it? 

 

Dataset: 

LabelValueYear

A402015
B302015
C102015
D502015
A302016
B202016
C602016
D302016

1.PNG

 

 

 

In the example above, I want add a tooltip for each Label - A,B,C,D individually to display the % for the specific year. Eg: For 2016, label A should display 30/140*100 = 21.42% as a tooltip and similarly rest of the labels too.

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
dedelman_clng New Contributor
New Contributor

Re: Display percentage in stacked column chart

 Make this measure:

 

 

PctGT =
DIVIDE (
    SUM ( 'Table'[Value] ),
    CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table'[Label] ) ),
    0
)

 

Capture.PNG

 

Hope this helps,

David

Super User
Super User

Re: Display percentage in stacked column chart

Hi @lit2018pbi,

 

Add this measure to your tooltip and format as %, should work.

 

percentage =
DIVIDE (
    SUM ( Table1[Value] );
    (
        CALCULATE (
            SUM ( Table1[Value] );
            ALL (  Table1[Label] );
            FILTER ( ALL ( Table1[Year] ); Table1[Year] = MAX ( Table1[Year] ) )
        )
    )
)

Regards,

MFelix



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

Proud to be a Datanaut!




dedelman_clng New Contributor
New Contributor

Re: Display percentage in stacked column chart

Same basic code, but make a measure first like:

 

MyCount = CALCULATE( COUNTA ('Table3'[ID] ) )

Then everywhere you have " SUM ('Table1'[Value] ) " replace it with the measure above.

 

PctGTCnt =
DIVIDE ( [MyCount], 
         CALCULATE ( [MyCount], ALL ( 'Table3'[Value] ) ),
         0 )

Capture.PNG

5 REPLIES 5
dedelman_clng New Contributor
New Contributor

Re: Display percentage in stacked column chart

 Make this measure:

 

 

PctGT =
DIVIDE (
    SUM ( 'Table'[Value] ),
    CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table'[Label] ) ),
    0
)

 

Capture.PNG

 

Hope this helps,

David

Super User
Super User

Re: Display percentage in stacked column chart

Hi @lit2018pbi,

 

Add this measure to your tooltip and format as %, should work.

 

percentage =
DIVIDE (
    SUM ( Table1[Value] );
    (
        CALCULATE (
            SUM ( Table1[Value] );
            ALL (  Table1[Label] );
            FILTER ( ALL ( Table1[Year] ); Table1[Year] = MAX ( Table1[Year] ) )
        )
    )
)

Regards,

MFelix



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

Proud to be a Datanaut!




lit2018pbi Frequent Visitor
Frequent Visitor

Re: Display percentage in stacked column chart

we have one more scenario like this which has date column (that we need to use on X-axis) is in another table and we dont have a numeric value in values. instead of that it showing categories, I have attached a picture for your reference. I wanted to display percent of that particular category individually  and considering that bucket itself as 100% as a tooltip in Power BI for a stacked column chart.

 

REF TABLE :

IDVALUEREF_DATE

AP1 TIMELY11/1/2016
BP1 UNTIMELY11/1/2016
CP2 TIMELY11/1/2016
DP1 TIMELY12/1/2016
EP1 UNTIMELY1/1/2017
FP2 TIMELY12/1/2016
GP1 TIMELY11/1/2016
HP2 UNTIMELY11/1/2016

 

DATE:

YEARMonthShort

2016Tuesday, November 01, 2016
2016Saturday, October 01, 2016
2016Thursday, September 01, 2016
2016Thursday, December 01, 2016
2017Sunday, January 01, 2017

 

viz:

 

2.PNG

dedelman_clng New Contributor
New Contributor

Re: Display percentage in stacked column chart

Same basic code, but make a measure first like:

 

MyCount = CALCULATE( COUNTA ('Table3'[ID] ) )

Then everywhere you have " SUM ('Table1'[Value] ) " replace it with the measure above.

 

PctGTCnt =
DIVIDE ( [MyCount], 
         CALCULATE ( [MyCount], ALL ( 'Table3'[Value] ) ),
         0 )

Capture.PNG

lit2018pbi Frequent Visitor
Frequent Visitor

Re: Display percentage in stacked column chart

As per the requirement, I need to sort those p1 timely,  p1 untimely, p2 timely and p2 untimely  in sorting order, but as the Power BI sorts automatically, it showing the visual in different sort order for the legend categories.
So I need to create measures to each one seperately as M_p1 timely,  M_p1 untimely, M_p2 timely and M_p2 untimely and added them in Value field. But now the DAX is not working even if i created a measure calculating the sum of all 4 measures and using that in the DAX you provided, Can you help me with that. Thanks in Advance