combined column/line chart based on percentages for both dimensions

[teinoB]

Hi all

I created the following combined column/line chart (showing the 3 NPS categories promoteres/indifferents/detractors in one dimension (left y axis) and the corresponding NPS value in the other dimension (right y axis) for each survey wave (="Tranche") on the x axis):

Question: is it possible (and if yes how to create it) to get the same chart, but based on percentages in both dimensions? It makes more sense to show the 3 NPS categories in percent which add up to 100% on the left y axis for each survey wave because the survey respondent base varies in absolute numbers from wave to wave.

Could anybody help me on this please? I didn't find a solution on the interenet or in Power BI so far. 

Thanks a lot and best Regards,

teinoB 

[darshaningale]

you can create a 100% stacked column chart.

If you mean you want to show values and percentages, then that is not possible.

[v-xiaotang]

Hi @teinoB

Here are 2 ways for your reference. The logic is similar, so you can adjust the method according to your needs.

(1) select Percent of GT, then open Data labels & Total labels. 

(2) the second way is to create a measure, such like  sum(each category each year) / sum(each year)

Just a gentle reminder: each column will be the same high in this scenario (see visual below). 

Test = 
VAR _total1 =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[year] = MIN ( 'Table'[year] )
                && 'Table'[category] = MIN ( 'Table'[category] )
        )
    )
VAR _total2 =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[year] = MIN ( 'Table'[year] ) )
    )
RETURN
    _total1/_total2

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[airetamstorm]

I applied this formula to my data and the results is a bit off.  I want the data shown in the upper left to be in a combo chart with a seperate cost line (shown in the bottom charts.  One differences in instead of summing I need to count distinct.

[teinoB]

Hi @v-xiaotang 

Your second solution shows exactly what I'm looking for! But I didn't get there unfortunately.

I created the measure below in my datasheet "Sheet1",  where "NPSGroup" is a numeric categorial variable from 1 to 3 (for each NPS category) and where "NPS Kategorien" is a verbalised categorial variable for the three categories containing the 3 describing category names "promoter", "neutral", "detractor" ; "Tranche" is the variable containing the yers/survey waves from 1 to 4:

NPSGroup% =

VAR _total1 =

CALCULATE (

SUM ( 'Sheet1'[NPSGroup]),

FILTER (

ALL ( 'Sheet1' ),

'Sheet1'[Tranche] = MIN ( 'Sheet1'[Tranche])

&& 'Sheet1'[NPS Kategorien] = MIN ( 'Sheet1'[NPS Kategorien])

)

)

VAR _total2 =

CALCULATE (

SUM ( 'Sheet1'[NPSGroup]),

FILTER ( ALL ( 'Sheet1' ), 'Sheet1'[Tranche] = MIN ( 'Sheet1'[Tranche]) )

)

RETURN

_total1/_total2

And now I get the following chart which shows the stacked columns summed up to 1 (not 100% as intended (why not?)) and doesn't show the correct fraction values for the 3 categories in each year (="Tranche", 1 to 4):

The correct absolute values are the following which aren't corresponging to the fraction values above:

Where went I wrong? Could you please help me again by adapting my syntax above correctly? I'm not very experienced so far in using DAX, sorry... 😉

Thanks a lot in advance for your hands-on support again and best Regards,

teinoB

[mh2587]

Change this measure to percentage measure tool in the ribbon by clicking the percent symbol

[teinoB]

Thanks, this worked, now it looks like this:

But still the values are not claculated correctly. The percentages don't correspond to the absolute values. Where may the problem be?

[v-xiaotang]

Hi @teinoB 

"The percentages don't correspond to the absolute values." what does it mean? could you write the correct percentages in the picture below? Thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[teinoB]

Hi

Considering the first column of the chart, we have 26.38% Promoters, 49.54% Neutrals and 24.08% Detractors.

But if we look at my original chart further above, where we find the origianl absolute counts, we have: 115 Promoters,  108 Neutrals and  35 Detractors, who sum up to 268 = 100%. Calculating the corresponding percentages provides: 42.91% Promoters, 40.29% Neutrals and 13.05% Detractors.

So I would like to have percentages which sum up to 100% within each column. 

How can I get this - how/wher do I have to adapt the syntax above in detail?

Thanks again!

[v-xiaotang]

Hi @teinoB 

There is only measure expression above, could you share some sample data then I can test.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[teinoB]

How to share data here? I have a dataset/pbix file ready. I'd be happy to let you take a look at the data. 

[v-xiaotang]

Hi @teinoB 

How to Get Your Question Answered Quickly - Microsoft Power BI Community

this article shares some ways to share the files, for your reference.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[teinoB]

Here we are, a sample of dummy data including only the relevant variables for creating the combined stacked column chart in percentages which should sum up to 100% for each wave (="Tranche").

Thansk again for your support.

Tranche	D06_1 : Weiterempfehlung	NPSGroup	NPS Kategorien
3	10	1	Promoter
2	8	2	Neutral
4	5	3	Detractor
2	9	1	Promoter
1	9	1	Promoter
3	10	1	Promoter
1	8	2	Neutral
2	8	2	Neutral
2	6	3	Detractor
1	8	2	Neutral
4	8	2	Neutral
2	10	1	Promoter
3	9	1	Promoter
2	10	1	Promoter
1	7	2	Neutral
3	99		missing
1	8	2	Neutral
2	9	1	Promoter
4	9	1	Promoter
2	6	3	Detractor
3	3	3	Detractor
1	8	2	Neutral
2	9	1	Promoter
4	9	1	Promoter
1	10	1	Promoter
4	7	2	Neutral
1	10	1	Promoter
3	8	2	Neutral
1	8	2	Neutral
4	9	1	Promoter
4	10	1	Promoter
4	10	1	Promoter
2	8	2	Neutral
1	9	1	Promoter
2	10	1	Promoter
2	10	1	Promoter
4	10	1	Promoter
1	6	3	Detractor
2	8	2	Neutral
1	10	1	Promoter
4	10	1	Promoter
4	9	1	Promoter
4	9	1	Promoter
2	8	2	Neutral
1	9	1	Promoter
1	7	2	Neutral
3	7	2	Neutral
4	5	3	Detractor
3	9	1	Promoter

[teinoB]

and that's the measure from above:

[teinoB]

and here's the other measure for calculating the NPS score for the values of the secondary axis on the right hand side, which works fine. But just to be complete with the infomration...

[teinoB]

@v-xiaotang 

Was this helpful for solving my problem? Thanks for your further support, I'm very thankful.

[amitchandak]

@teinoB , You have to create a %  measure and use that in visual and switch off secondary axis

divide([Measure], calculate([measure], removefilters(Table[NPS categories]) )

[teinoB]

Thanks for your answer!

Could you give me a step by step instruction please as I'm a newby in Power BI... 😉

Why do I have to switch off the secondary axis? I'd like to keep it as want to display the NPS value on the right hand side (or as a second dimension). How can I create this?

Thanks for your answer and cheers

teinoB