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

[jengwt]

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])
        , "CAD"
            , 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).

Thanks for your help!

[jengwt]

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])))

[jengwt]

@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

[v-yangliu-msft]

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.