Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jengwt
Helper V
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:

jengwt_0-1669820019459.png

 

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:

jengwt_2-1669820621628.png

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!

3 REPLIES 3
jengwt
Helper V
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])))

jengwt
Helper V
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
P1Bob29/12/2021$4
P1Joe1/1/2022$6
P2Joe29/12/2021$3
P2Bob1/1/2022$6
P3Sally29/12/2021$2
P3Joe2/1/2022$8
P4Bob30/12/2021$7
P4Joe2/1/2022$2
P5Joe31/12/2021$8
P5Sally3/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
P1C1B1
P2C1B1
P3C2B2
P4C2B3
P5C3B4
P6C4B4

 

Dim Customer

Customer Country
BobUSA
JoeCanada
SallyMexico
FrankUSA
MonicaCanada
LawryMexico

 

Dim Date

Date Month Year
29/12/2021Dec2021
30/12/2021Dec2021
31/12/2021Dec2021
1/1/2022Jan2022
2/1/2022Jan2022
3/1/2022Jan2022

 

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
USABob2021C1B1P1$4 
USABob2021C2B2P4$7 
CanadaJoe2021C3B4P5 $8
MexicoSally2022C3B4P5 $1
Total     $11$9
v-yangliu-msft
Community Support
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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors