Forum Discussion
Measure that sums different columns as values based on filter
I am trying to create a measure which sums values of different columns based on filter. Say I have 2 tables:
Currency table
| CurrencyCode |
| USD |
| EUR |
Sales table
| RevenueUSD | RevenueEUR |
| 10 | 9 |
| 50 | 47 |
I need to create a measure that would SUM the revenue from appropriate column when Currency table is applied as filter (i.e. when it's set as USD - measure sums RevenueUSD column, when it's set as EUR - measure sums RevenueEUR column). Is there any way to achieve this?
Thanks for help
Hello nesisekasi,
Please find the below solution that works. Also, please note this will work only for Single Selection in the Filter:Here is the formula for the measure:
DynamicRevenue_Sum =-- Creating variables for each sumVAR USD_SUM = SUM(Sales[Revenue_USD])VAR EUR_SUM = SUM(Sales[Revenue_EUR])-- Identifying Selected Value from the filterVAR SelectedFilter = SELECTEDVALUE('Currency'[CurrencyCode])RETURN-- Returning sum value based on the selectionIF(SelectedFilter = "USD", USD_SUM, EUR_SUM)
Please mark this as solution if it helps.
Thank you,Pradeep Chowdavarapu
"Even if no one believes you can,YOU GOT TO BELIEVE YOU CAN."
1 Reply
Hello nesisekasi,
Please find the below solution that works. Also, please note this will work only for Single Selection in the Filter:Here is the formula for the measure:
DynamicRevenue_Sum =-- Creating variables for each sumVAR USD_SUM = SUM(Sales[Revenue_USD])VAR EUR_SUM = SUM(Sales[Revenue_EUR])-- Identifying Selected Value from the filterVAR SelectedFilter = SELECTEDVALUE('Currency'[CurrencyCode])RETURN-- Returning sum value based on the selectionIF(SelectedFilter = "USD", USD_SUM, EUR_SUM)
Please mark this as solution if it helps.
Thank you,Pradeep Chowdavarapu
"Even if no one believes you can,YOU GOT TO BELIEVE YOU CAN."