Display percentage in stacked column chart

[lit2018pbi]

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

A	40	2015
B	30	2015
C	10	2015
D	50	2015
A	30	2016
B	20	2016
C	60	2016
D	30	2016

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.

[dedelman_clng · Accepted solution]

 Make this measure:

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

Hope this helps,

David

[MFelix · Accepted solution]

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

[jgubler]

If your bars are categotical instead of numerical:

Percent_mar =

DIVIDE (

COUNT(Query1[Gerneral Marital Status] ),

CALCULATE ( COUNT ( Query1[Gerneral Marital Status] ), ALL(Query1[TMS_filter])) ,

0

)

This is based off of @dedelman_clng 's answer above

[MFelix · Accepted solution]

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

[lit2018pbi]

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

A	P1 TIMELY	11/1/2016
B	P1 UNTIMELY	11/1/2016
C	P2 TIMELY	11/1/2016
D	P1 TIMELY	12/1/2016
E	P1 UNTIMELY	1/1/2017
F	P2 TIMELY	12/1/2016
G	P1 TIMELY	11/1/2016
H	P2 UNTIMELY	11/1/2016

DATE:

YEARMonthShort

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

viz:

[dedelman_clng · Accepted solution]

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 )

[dedelman_clng · Accepted solution]

 Make this measure:

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

Hope this helps,

David