Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
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.
Proud to be a 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
vResult
Visuals:
The Currency slicer uses Currency[Currency].
Matrix:
------------------------------------------------------------
-------------------------------------------------------------
Proud to be a Super User!
I 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) |
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |