Sum of measures containing 'All'

[emizlp]

Hi everyone,

I am making a Power BI report, where the goal is to calculate the difference between a "global" grouped average and the value of the single orderline, called 'Lost Saving'.

I am working in a database, and it is protected by Row Level Security, so to surpass this, we have created a new table every row has been duplicated, so it now contains a line with "personal" information and a cloned line stripped of every relation to any personal information. Every member would be able to see the anonymous lines, but only the "personal" lines appurtenant to their corporate.

This is the code I use to get the total value spend on a given product (travel), across all corporates (by using the "fake" corporate 'Anonymous'.

CALCULATE (
    SUM ( 'Air Orderline Benchmark Data'[VendorValueDKK] )
        + SUM ( 'Air Orderline Benchmark Data'[VendorValueDKKVAT] ),
    ,FILTER(ALL(Corporate[CorporateName]),Corporate[CorporateName]="Anonymous")
    ,ALL('Air Orderline Benchmark Data'[OrderlineNo])
    ,ALL(Requester[RequesterFullName])
    ,All(Corporate[CountryName])
    ,ALL('Air Orderline Benchmark Data'[DaysPresale])
    ,ALL('Air Orderline Benchmark Data'[PolicyOrderTimeDays])
    ,ALL(Corporate[EntityName])
    ,ALL(Traveller[TravellerFullName])
)

This works, and by applying the same procedure I can also get a tally of how many times this "product" has been bought, and by that get the avg.

So I have succeeded "most" of the way, but when I, in the end, want to get the grand total of the 'Lost Saving' in a separate "card", I get a completely wrong answer. 

I suspect it is due to the 'All' functions still affecting my calculation of the lost saving, but I do not know how to work around this.
So I kindly ask all you smart people for assistance, and I will try and provide additional information if needed.
BR Emil, the desperate BI Man 😄 

[v-binbinyu-msft]

Hi @emizlp ,

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.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu

[emizlp]

I can try 🙂 

We are selling travels. And have a policy on how far in advance the tickets should be booked. 
We then want to show how much you "lose" by booking later than the policy.
To do this, we take your order and look at what route you flew, let's say London - NY, but booked late.
Then I want to make an average from all of our members who flew the route London - NY.
The difference between those two numbers is then the Lost Saving.

What makes this problematic is, that the entire database is "protected" by RLS, so members from different companies can't see data, from other companies than their own. 

As I wrote above, this has been worked around, by cloning all orders, but this time with no information of company, name etc, so anonymous data.

I have made all the calculations work, besides getting the right sum, both in the tabel and in a card.

In this example I just want it to sum 434 + 2569 = 3003 and not = 4030
(Worth mentioning that to get the global avg, I am subtracting that specific order)

I don't know how to best do a desensitized example data set, since it is data from 4 different tables all connected. But if you have an idea to do so, please let me know, and I will do that, and provide it 🙂 

[v-binbinyu-msft · Accepted solution]

Hi @emizlp ,

Because the information limit, i will try my best to give you the solution.

According to your description, the problem is the total sum not correct, i assume "Routing_Id_debtorHome_Name_Sorted" is a field, "Lost Savings" is a measure.

Please try below dax formula:

Adjust Lost Savings =
SUMX ( VALUES ( [Routing_Id_debtorHome_Name_Sorted] ), [Lost Savings] )

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.