Cannot sort by measure based on another measure

[cuongle]

I got stuck on how to sort by measure based on another measure. On my case I have 2 measures called: Savings and Spend.

In order to support bar chart:

Whenever end-users choose 'Sort by Saving' I would like to sort by Spend behind the scene. I know Power BI support function called 'Sort By Column' on modeling but it's disable if I choose measure `Saving`

Is there anyway to work around for this case?

Thanks a lot

[Anonymous]

Hi @cuongle,

I think this function only works on columns.(if you try to expand the dropdown panel, you can only find the columns)

Regards,

Xiaoxin Sheng

[cuongle]

Thanks @Anonymous, do you know is there any way to work around with this? I cannot use column as measure `Spend` and `Savings` is dynamically calculated based on the currency that user chooses.

[Anonymous]

Hi @cuongle,

Can you share some detail contents about this formulas and table structures? If these measures not the dynamic measure which changes by slicer, you can modify the formula to transform it to calculate column.

Regards,

Xiaoxin Sheng

[cuongle]

@Anonymous

Assume I have table called InvoiceDetail, which contain column:

SpendInEUR

SpendInGBP

SpendInUSD

SavingsInEUR

SavingsInGBP

SavingsInUSD

So on the Power BI, we allow user chooses which currency they want to show using ChicletSlicer like that:

And then we calculate measure Spend and Savings based on which currency is opted

Spend = 
IF (
    LASTNONBLANK ( 'DefaultCurrency'[Code], 1 ) = "GBP",
    SUM ( InvoiceDetail[SpendInGBP]),
    IF (
        LASTNONBLANK ( DefaultCurrency[Code], 1 ) = "USD",
        SUM ( InvoiceDetail[SpendInUSD] ),
        SUM ( InvoiceDetail[SpendInEUR] )
    )
) 

Savings = 
IF (
    LASTNONBLANK ( 'DefaultCurrency'[Code], 1 ) = "GBP",
    SUM ( InvoiceDetail[SavingsInGBP]),
    IF (
        LASTNONBLANK ( DefaultCurrency[Code], 1 ) = "USD",
        SUM ( InvoiceDetail[SavingsInUSD] ),
        SUM ( InvoiceDetail[SavingsInEUR] )
    )
)

Thanks for your help

[Rylee]

@cuongle Did you ever find a trick to work around this? I am dealing with the exact same situation.

[Anonymous]

Hi @cuongle,

Power bi not support dynamic calculated column/table based on slicer, so it is impossible to convert your formula to calculate column.

Reference link:

Calculated Column/Table Change Dynamically According to Slicer Selection in the Report.

Regards,

Xiaoxin Sheng