Row total 100% Stacked Bar Chart (ignore legend)

[joell]

Hi all,

I am having an issue with calculating the row total of a 100% stacked bar chart, ignoring the legend but which should be adjustable when selecting a slicer on my report:

Total row value is 694 for value KA3 with legend value V.

As you can see at top left there is a visual with the values KA1, KA2, KA3 (columnname: IndicatorCodeOrigineel). This visual is calculating unique id numbers (columnname: Onderzoeks_Nr). And there is a legend value with Score code (columnname: ScoreOms) which divides the values in O, V, G.

In the tooltip there is a value Aantal onderzoek (N=), which shows the total amount of the unique id numbers (distinctcount). This works fine. But I also want to provide an extra tooltip value with the total row numbers for example KA1, KA2, KA3, ignoring the legend. This is the tooltip value Aantal onderzoeken (N=) over indicator Totaal. Therefor I created the following measure:

Aantal onderzoeken (N=) over indicator Totaal (BST):=
                                CALCULATE(DISTINCTCOUNT(Onderzoek[OnderzoeksNr]);
                                                    ALLEXCEPT(Onderzoek;Onderzoek[IndicatorcodeOrigineel];
                                                                       Onderzoek[Schooljaar]; -- Slicer Schooljaar
                                                                       Onderzoek[OnderzoekstypeOms]; -- Slicer Onderzoekstype
                                                                       Onderzoek[LeidendeSector]; -- Slicer LeidendeSector
                                                                       Onderzoek[Kantoor]); -- Slicer Kantoor
                                                                       (Onderzoek[FK_SectorCode]) = "BST";
                                                                       (Onderzoek[Elementtype]) = "BGE"
                                                                       )
As you can see I used the ALLEXCEPT function to make sure the row total of the measure fits the slicers. But the issue I am facing is when there is no value in a specific slicer it drops the row total of my measure, like here:


Total row value is 656 for value KA3 and legend value G

As you can see above, the same visual with KA3 has now a lower value in the tooltip for legend value G. In the first screenshot the total row value for KA3 with V = 694 (also for value O). The difference arises because there is no value for KA3 with value G in one of the slicers (Leidende Sector = SO):

There is no value for KA3 with legend value G when selecting a slicer value, in this case Leidende Sector = SO.

Conclusion: I would like to have a row total value for a 100% Stacked Bar Chart. Which must be adjustable when using the slicers, but always must show the same total row value in the tooltip ignoring the legend (Score O,V,G).

Is it also possible to add a row total value at a matrix where I transformed the value to a Percent of row total:


Same values, only showed in a matrix. 

Many thanks!

Joël

[amitchandak]

Check if these solutions can help

https://community.powerbi.com/t5/Desktop/100-Stacked-bar-chart-display/m-p/853213

https://www.youtube.com/watch?v=8fgblujBoHg

https://community.powerbi.com/t5/Desktop/100-Stacked-bar-chart/m-p/905132

[joell]

@amitchandak thanks for your reply, unfortunately it doesn't provides a solution for my issue