Using calculation groups in a new way

[CL7777]

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,

[DataInsights]

@CL7777,

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:

------------------------------------------------------------

-------------------------------------------------------------

[CL7777]

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)

[DataInsights · Accepted solution]

@CL7777,

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.