Forum Discussion
Using calculation groups in a new way
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.
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:
------------------------------------------------------------
-------------------------------------------------------------
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.
