Forum Discussion
cuongle
Advocate II
9 years agoCannot sort by measure based on another measure
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 cho...
cuongle
Advocate II
9 years agoAnonymous
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