cancel
Showing results for
Did you mean:
Helper V

## How to calculate two sets of measures from the same fact, and then compare them

Hello community,

I'm trying to figure out how to create a report with visuals which will allow my users to compare two sets of products/scenarios. Currently, I am attempting to do this with one invoice fact and two sets of dimensions, each with their own sets of measures. Here is a sample of how my dataset looks:

Here is a sample of how the two sets of measures look:

``````Invoiced Amount 1 = VAR C = [Selected Currency]
RETURN CALCULATE(SWITCH(C
, "USD"
, SUM('Fact Invoice Sales'[Invoice Amount USD])
, SUM('Fact Invoice Sales'[Invoice Amount CAD])
, C)
, USERELATIONSHIP('Fact Invoice Sales'[Invoice Date SK], 'Dim Date 1'[Date SK])
, USERELATIONSHIP('Fact Invoice Sales'[Division SK], 'Dim Division 1'[Division SK])
, USERELATIONSHIP('Fact Invoice Sales'[Customer Key Account SK], 'Dim Customer Key Account 1'[Customer Key Account SK])
, USERELATIONSHIP('Fact Invoice Sales'[Invoiced Customer SK], 'Dim Invoiced Customer 1'[Invcd Customer SK])
, USERELATIONSHIP('Fact Invoice Sales'[Warehouse SK], 'Dim Warehouse 1'[Warehouse SK])
, USERELATIONSHIP('Fact Invoice Sales'[Product SK], 'Dim Product 1'[Product SK])
--, ALL('Fact Invoice Sales') -- Test. Didnt work, cartesianing.
)``````

Ideally I would be able to get both sets of calculations into one matrix/table visual, but currently I can use measures like the one above, just with two sets of visuals; one for set 1, and the other for set 2, controlled by editing the visual interactions:

Side note: in this context, the above USERELATIONSHIPS are superfluous. But I thought that I would need them to accomplish what I'm trying to do; read on.

However, this does not allow for direct comparisons between products. For instance, say that I want to compare the ratio of sales from product 1 to product 2. I might use something like this:

``Invoiced Amount Delta = [Invoiced Amount 1] - [Invoiced Amount 2]``

There are two problems:

1. I could not have a measure like this in the same visual as Invoiced Amount 1 and Invoiced Amount 2 because it wouldn't make sense in the presented context (by product), even if I could get around the problem of:
2. I don't know how to create a context in which PBI knows to take the filters on Invoiced Amount 1 and then Invoiced Amount 2 seperately, and then smash them together to get the delta. I can't use Product 1 Slicer and Product 2 Slicer on the same visual because the contexts will conflict.

Any ideas on how to get around this?

I would also be content if I could just get a set of cards to show the total comparisons between the two set of calculations, but I still run into the context problem.

Alternatively, I could load the fact table twice and run the measures against each of them separately, but my fact table is large, and I'm only working with pro licenses here (1GB dataset limit).

3 REPLIES 3
Helper V

Another idea just occurred to me. I might try using something like a CALCULATE(SUM('fact'[field]), FILTER(ALL('fact'), 'fact'[dim1 sk] IN VALUES('dim1'[sk])))

Helper V

@v-yangliu-msft Ok, for a simplistic example let's say this is my fact table:

Fact Sales

 Part Customer Sale Date (D/M/YYYY) Sale Amount P1 Bob 29/12/2021 \$4 P1 Joe 1/1/2022 \$6 P2 Joe 29/12/2021 \$3 P2 Bob 1/1/2022 \$6 P3 Sally 29/12/2021 \$2 P3 Joe 2/1/2022 \$8 P4 Bob 30/12/2021 \$7 P4 Joe 2/1/2022 \$2 P5 Joe 31/12/2021 \$8 P5 Sally 3/1/2022 \$1

Total sales are \$47

Then we have these dimension tables:

In my current interation, I actually have two sets of the these dimensions, as I attempt to get this to work. One set to filter to get Sales 1 and another to get Sales 2 (see below).

Dim Part

 Part Part Category Part Brand P1 C1 B1 P2 C1 B1 P3 C2 B2 P4 C2 B3 P5 C3 B4 P6 C4 B4

Dim Customer

 Customer Country Bob USA Joe Canada Sally Mexico Frank USA Monica Canada Lawry Mexico

Dim Date

 Date Month Year 29/12/2021 Dec 2021 30/12/2021 Dec 2021 31/12/2021 Dec 2021 1/1/2022 Jan 2022 2/1/2022 Jan 2022 3/1/2022 Jan 2022

What my users want is to be able to do is, for example, compare US purchases (Customer Bob) from 2021 to all purchases of Brand 4 (Part 5).

US customers (Bob) made two purchases in 2021, yielding \$11. \$9 of Brand 4 (Part 5) were sold. I can calculate these numbers independently using separate visuals and editing the visual relationships (i.e. the second screenshot in the OP), but I can't compare the two numbers directly because their contexts are mutually exclusive; Bob purchased no Part 5 and made no purchases in 2022.

In principle this is what I'm trying to do.

You and I know that the difference between these two numbers is \$11 - \$9 = \$2, but I can't get PBI to tell me that.

I've tried using ALLs in my DAX as well (see first code sample in the OP), but it causes relationship or cartesianing problems; I just get \$47 back regardless of filtering on Part, Category, Brand, Customer, Country, or Year.

If I could just, say, get a card to show that difference of \$2, then I would be well on my way.

Ideally, my stakeholders would like a table visual like this (using the above example):

 Country Customer Sale Year Category Brand Part Sales 1 Sales 2 USA Bob 2021 C1 B1 P1 \$4 USA Bob 2021 C2 B2 P4 \$7 Canada Joe 2021 C3 B4 P5 \$8 Mexico Sally 2022 C3 B4 P5 \$1 Total \$11 \$9
Community Support

Hi  @jengwt ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.

For the USERLATIONSHIP() function, create a measure to place one, if it is more than one, it may be confusing because of the relationship between tables.

For the Sum() function, on the measure is the current value, if you want to group by Product you can try the following.

SUMX(Filter(All('Fact Invoice Sales'), Fact Invoice Sales‘[product])=MAX('Fact Invoice Sales'[product]), [Invoice Amount USD])

USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

SUMX function (DAX) - DAX | Microsoft Learn

MAX function (DAX) - DAX | Microsoft Learn

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors