Sorting stacked bar chart by total not correct for calculated measure

[kkashiv]

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?

[Jihwan_Kim]

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.

[kkashiv]

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. 

[kkashiv]

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?

[Jihwan_Kim · Accepted solution]

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:]
                )
            )
        )
    )
)

[kkashiv]

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

[Jihwan_Kim]

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.