cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CL7777
Helper III
Helper III

Using 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,

1 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.

View solution in original post

3 REPLIES 3
DataInsights
Super User
Super User

@CL7777,

 

This solution uses two disconnected tables:

 

1. Currency

 

DataInsights_1-1626962027850.png

 

2. ColumnHeader

 

DataInsights_2-1626962041684.png

 

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:

 

DataInsights_6-1626962566213.png

 

 

DataInsights_3-1626962209428.png

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

 

DataInsights_4-1626962235888.png

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

DataInsights_5-1626962256370.png

 

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 numberList PriceDiscountNet price
A1 (Display Amount) (Display Amount) (Display Amount)
A2 (Display Amount) (Display Amount) (Display Amount)
B3 (Display Amount) (Display Amount) (Display Amount)
B4(Display Amount)(Display Amount)(Display Amount)
B5(Display Amount)(Display Amount)(Display Amount)

@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.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!