How to include the grand total in a stacked bar graph

[QinisileD]

Hello,

How can I ensure that the created stacked bar graph also displays the grand total?

I am using survey data, I did a crosstab (matrix) of 2 variables Province and Food adequancy and the measure is weighted food.

I want to populate the above results in a stacked bar graph, however, I am struggling to figure out how I can also display the Total "South Africa"

Kindly assist.

have 9 provinces and

[v-cazheng-msft]

Hi @QinisileD 

I think you need create a new table in which contains a row with the values of total and then use it to create your visual. Here is a similar issue and there is a solution in it, you refer Grand Total to Stacked Column Graph.

If you still have questions, please don't hesitate to let me known and please provide a small part of sample data that does not involve sensitive information.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[QinisileD]

I am a beginner in using Power bi, not sure if this is the correct way to share the data. Kindly review the data on the attached link

https://app.powerbi.com/reportEmbed?reportId=42e89bd8-77db-4ad1-a62f-f876373a79ee&autoAuth=true&ctid...

https://app.powerbi.com/groups/me/reports/42e89bd8-77db-4ad1-a62f-f876373a79ee 

<iframe width="1140" height="541.25" src="https://app.powerbi.com/reportEmbed?reportId=42e89bd8-77db-4ad1-a62f-f876373a79ee&autoAuth=true&ctid..." frameborder="0" allowFullScreen="true"></iframe> 

[v-cazheng-msft]

@QinisileD 

Sorry, but I can't reach it. You can add tables in your reply and share some sample data in the form of table.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[QinisileD]

Here is the table:

uniqueid	year	province_no	benchwght	food_adequency	province	food	latitude	longitude
6713	2016	1	14423,26467	3	Western Cape	More than adequate	22,07060	-32,66060
6918	2016	1	7415,942094	2	Western Cape	Just adequate	22,07060	-32,66060
7	2016	4	16616,75249	2	Free State	Just adequate	26,52710	-28,70080
240	2016	4	7508,74183	1	Free State	Inadequate	26,52710	-28,70080
609	2016	2	237,5502459	2	Eastern Cape	Just adequate	26,57030	-32,13230
1277	2016	2	138,7312867	9	Eastern Cape	No answer	26,57030	-32,13230
1279	2016	2	83,12190439	1	Eastern Cape	Inadequate	26,57030	-32,13230
1443	2016	7	5055,780992	1	Gauteng	Inadequate	28,01150	-26,01940
1444	2016	7	7583,671831	2	Gauteng	Just adequate	28,01150	-26,01940
1445	2016	7	5027,815557	2	Gauteng	Just adequate	28,01150	-26,01940
1253	2016	2	2057,632024	3	Eastern Cape	More than adequate	26,57030	-32,13230
1586	2016	7	574,9823174	1	Gauteng	Inadequate	28,01150	-26,01940
2647	2016	5	7076,584209	1	KwaZulu-Natal	Inadequate	30,44700	-28,96510
2648	2016	5	7493,648472	2	KwaZulu-Natal	Just adequate	30,44700	-28,96510
2650	2016	5	2175,392186	1	KwaZulu-Natal	Inadequate	30,44700	-28,96510
2665	2016	5	4197,647689	3	KwaZulu-Natal	More than adequate	30,44700	-28,96510
4215	2016	9	928,0882838	2	Limpopo	Just adequate	29,34090	-23,78450
4219	2016	9	192,8319137	2	Limpopo	Just adequate	29,34090	-23,78450
4220	2016	9	1346,399305	3	Limpopo	More than adequate	29,34090	-23,78450
4291	2016	9	4493,320878	1	Limpopo	Inadequate	29,34090	-23,78450
4611	2016	8	472,4340526	1	Mpumalanga	Inadequate	30,13780	-25,75690
5409	2016	3	1927,742277	3	Northern Cape	More than adequate	21,97490	-28,93660
5477	2016	3	521,5076894	3	Northern Cape	More than adequate	21,97490	-28,93660
6023	2016	6	2123,851658	2	North West	Just adequate	25,23470	-26,38560
6027	2016	6	5665,725727	3	North West	More than adequate	25,23470	-26,38560
6093	2016	1	11974,79018	3	Western Cape	More than adequate	22,07060	-32,66060
6483	2016	1	375,7388954	3	Western Cape	More than adequate	22,07060	-32,66060
225	2016	4	6788,06939	3	Free State	More than adequate	26,52710	-28,70080
230	2016	4	1323,755795	1	Free State	Inadequate	26,52710	-28,70080
4581	2016	8	3350,95021	2	Mpumalanga	Just adequate	30,13780	-25,75690
4597	2016	8	1269,422336	2	Mpumalanga	Just adequate	30,13780	-25,75690
4607	2016	8	333,5449606	2	Mpumalanga	Just adequate	30,13780	-25,75690
5143	2016	3	7182,043221	1	Northern Cape	Inadequate	21,97490	-28,93660
5144	2016	3	1141,144307	1	Northern Cape	Inadequate	21,97490	-28,93660
5855	2016	6	22089,83831	1	North West	Inadequate	25,23470	-26,38560
5880	2016	6	1586,629513	9	North West	No answer	25,23470	-26,38560
5884	2016	6	2171,599386	2	North West	Just adequate	25,23470	-26,38560

[QinisileD]

I am not sure why the Table is not in the correct Table format. Kindly note that the last digit/number of the benchwght relates to food_adequency (1-Inadequate, 2- Just adequate, 3- More than adequate, 8- do not know, 9- no answer).

[v-cazheng-msft · Accepted solution]

Hi @QinisileD 

Sorry for late reply! You can create a Calculated column and create a Measure, then use them to create your visual.

The Calculated table:

Table =

UNION (

    SELECTCOLUMNS (

        Data,

        "province", Data[province],

        "food", Data[food],"weight",Data[benchwght]

    ),

    UNION (

        ROW (

            "province", " Total",

            "food", "No answer","weight",CALCULATE ( SUM (Data[benchwght]), FILTER ( Data, Data[food] = "No answer" ) )

        ),

        ROW (

            "province", " Total",

            "food", "Inadequate","weight",CALCULATE ( SUM (Data[benchwght]), FILTER ( Data, Data[food] = "Inadequate" ) )

        ),

        ROW (

            "province", " Total",

            "food", "Just adequate","weight",CALCULATE ( SUM (Data[benchwght]), FILTER ( Data, Data[food] = "Just adequate" ) )

        ),

        ROW (

            "province", " Total",

            "food", "More than adequate","weight",CALCULATE ( SUM (Data[benchwght]), FILTER ( Data, Data[food] = "More than adequate" ) )

        )

    )

)

The Measure:

Weighted =

VAR val1 =

    SUM ( 'Table'[weight] )

VAR val2 =

    CALCULATE ( SUM ( 'Table'[weight] ), ALLEXCEPT ( 'Table', 'Table'[province] ) )

RETURN

    val1 / val2

The result looks like this:

For more details, you can refer the attached pbix file.

Best Regards

Caiyun Zheng

 If this post helps, then please Accept it as the solution. Really appreciate!

[QinisileD]

Hi Caiyun,

Thanks a lot. What you recommended actually worked 👍. 

Is there a way in which I can use a slicer to filter with the created graph.

I would like the results to change as I select different years. 

Regards,

QinisileD

[selimovd]

Hey @QinisileD ,

can you show how the data looks like? What of those are columns, what measures, what hierarchies etc.?

Then it's easier get an idea. Usually the last row in a matrix should say "Total" and not "South Africa", so it's worth investigating the data.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[QinisileD]

Thank you for assisting, basically when power bi produced the matrix results I clicked the the Format icon and under subtotals (row subtotal labels) I changed "Total" to "South Africa" because South Africa has 9 provinces and the total should represent the country as a whole.

I am using survey data with 47752 cases and numerous variables, I do not know how to share it on this platform. I used

Column: Province - where people had to chose between 1-9 provinces to select their location;

Column: Food - people had to chose between 1-5 (1-inadequete, 2- just adequate, 3- more than adequete, 4 - do not know & 5- No answer); and 

A weight variable: benchwght

On the matrix table : Under rows I have (Province), columns (Food) and values (a measure which I calculated -

Weighted Food = DIVIDE(SUM('Data'[benchwght]), CALCULATE(SUM(Data[benchwght]),ALLSELECTED(Data[Food])))

My problem is that the stacked bar graph only shows the results from the 9 provinces and excludes the Total which in this case I named it South Africa.

I would like to produce a graph that is similar to the one below: 

I can change the names of the Provinces to abbreviations later.