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:
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!
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])))
@v-yangliu-msft Ok, for a simplistic example let's say this is my fact table:
|Part||Customer||Sale Date (D/M/YYYY)||Sale Amount|
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).
|Part||Part Category||Part Brand|
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|
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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.