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'.
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 😄
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 🙂