Forum Discussion
Helper IIIUsing calculation groups in a new way
Hello all.
I am trying to use calculation groups in a nested sort of way. I am not new to power BI or Dax but I am relatively new to calculation groups.
In my data, I have columns that have different values for different currencies. Lets say the names of the columns are:
ListPriceUSD
ListPriceGBP
ListPriceEUR
DiscountUSD
DiscountGBP
DiscountEUR
NetPriceUSD
NetPriceGBP
NetPriceEUR
I have a table in my dashboard that displays the following as column headers "company name", "list price" , "discount", "net price"
What I want is to be able to select a slicer "USD", "GBP", or "EUR" and have the table display the sum of the correct list price, discount, and net price... So, for example, if I select "EUR", I want the table in my dashboard to show the sum for ListPriceEUR, DiscountEUR, and NetPriceEUR.. If I select "USD", I want the table to show the sums for ListPriceUSD, DiscountUSD, and NetPriceUSD - the same for when I select GBP.
I know I can do this with calculation groups so I would love to figure out how or if there is an alternative solution without calculation groups, Im ok with that as well
I appreciate your help very much,
Glad to hear it works. You can add any column to the matrix rows and the measure Display Amount will slice the amount based on whatever values are in the matrix rows. There's no need to create another table.
3 Replies
- DataInsights
Super User
This solution uses two disconnected tables:
1. Currency
2. ColumnHeader
Create measures for each amount column (example below):
Discount EUR = SUM ( FactTable[DiscountEUR] )Create measure to display in visual:
Display Amount = VAR vHeader = SELECTEDVALUE ( ColumnHeader[Header] ) VAR vCurrency = SELECTEDVALUE ( Currency[Currency] ) VAR vResult = SWITCH ( TRUE (), vHeader = "List Price" && vCurrency = "GBP", [List Price GBP], vHeader = "List Price" && vCurrency = "EUR", [List Price EUR], vHeader = "List Price" && vCurrency = "USD", [List Price USD], vHeader = "Discount" && vCurrency = "GBP", [Discount GBP], vHeader = "Discount" && vCurrency = "EUR", [Discount EUR], vHeader = "Discount" && vCurrency = "USD", [Discount USD], vHeader = "Net Price" && vCurrency = "GBP", [Net Price GBP], vHeader = "Net Price" && vCurrency = "EUR", [Net Price EUR], vHeader = "Net Price" && vCurrency = "USD", [Net Price USD] ) RETURN vResultVisuals:
The Currency slicer uses Currency[Currency].
Matrix:
------------------------------------------------------------
-------------------------------------------------------------
CL7777I got it working. thank you.. One more follow up to the question. What if I have another row header. so I want the Display Amount to be for each company AND each invoice within that company.. so, the matrix would look like this (see table below). Would I have to create another table with all the invoice numbers?
company invoice number List Price Discount Net price A 1 (Display Amount) (Display Amount) (Display Amount) A 2 (Display Amount) (Display Amount) (Display Amount) B 3 (Display Amount) (Display Amount) (Display Amount) B 4 (Display Amount) (Display Amount) (Display Amount) B 5 (Display Amount) (Display Amount) (Display Amount) - DataInsights
Glad to hear it works. You can add any column to the matrix rows and the measure Display Amount will slice the amount based on whatever values are in the matrix rows. There's no need to create another table.