topic Re: Sorting stacked bar chart by total not correct for calculated measure in DAX Commands and Tips

Sorting stacked bar chart by total not correct for calculated measure

Anonymous — Wed, 07 Jun 2023 04:09:04 GMT

PowerBI novice here. I have a measure named 'Measure_2' that displays this information where commodity( Electric, Natural Gas, Steam, etc) is the legend for stacked bar chart.

This is the data in a tabular format.

I would like to sort this by the total of all columns for individual Building. Basically keep the longest bar first.
I have selected the sort by Measure 2 in descending order

However this doesnt sort the stacked bar chart in the correct order even though the totals above each bar is correct. Is there a way to achieve this?

Re: Sorting stacked bar chart by total not correct for calculated measure

Jihwan_Kim — Tue, 06 Jun 2023 03:24:14 GMT

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbixfile.

Re: Sorting stacked bar chart by total not correct for calculated measure

Anonymous — Tue, 06 Jun 2023 03:49:16 GMT

Thanks for replying. Your data model is exactly similar to mine. The only difference is that I am using a measure called 'Measure_2' that utilizes another measure 'Bill_line_items_value' and converts values to the same unit.

DAX expression of the measure:

If I use the original measure 'Bill_line_item_value', it sorts the bars correctly:-

The sorting is failing for the new measure 'Measure_2' I created for standardizing the values.

Re: Sorting stacked bar chart by total not correct for calculated measure

Anonymous — Tue, 06 Jun 2023 04:29:06 GMT

Using your PBIX file, I created a similar measure. It messes up the sorting with your data too.

Attaching the pbix file with the measure: kkashiv.pbix
Is it because of the DAX expression?

Re: Sorting stacked bar chart by total not correct for calculated measure

Jihwan_Kim — Wed, 07 Jun 2023 03:10:28 GMT

Hi,

Please kindly check if the below measure works as expected with showing the correct numbers.

Measure_2 = SUMX ( SUMMARIZE ( Data, Buildings[Buildings], Commodity[Commodity] ), IF ( Commodity[Commodity] = "Electric", [Value measure:] * 3.412, IF ( Commodity[Commodity] = "Natural Gas", [Value measure:] * 100, IF ( Commodity[Commodity] = "Steam", [Value measure:] * 1194, IF ( Commodity[Commodity] = "Chilled Water", Data[Value measure:] * 12, [Value measure:] ) ) ) ) )

Re: Sorting stacked bar chart by total not correct for calculated measure

Anonymous — Wed, 07 Jun 2023 03:15:42 GMT

Yes this works. Thank you so much. I would like to understand the difference in the DAX expression. What exactly changed?

Re: Sorting stacked bar chart by total not correct for calculated measure

Jihwan_Kim — Wed, 07 Jun 2023 03:23:10 GMT

Hi,
It changed to produce the total number of each building. If you try to change the visualization to a table visualization, and if you see the Total Row in the visualization, two measures show different total number.

I think, the one you provided showed correct numbers in each row, however, it showed an incorrect number in the total row.