I initially thought this would be an easy one, but I am failed to resolve this issue.
My initial data has 3 rows for Profit center numbers and a corresponding allocation by profit center. ItThese allocation should be multiplied by a measure that was already set up.
Profit centers can appear in every profit center column but can also be different.
My goal is to get the revenue by profit center.
To do that, I started to create measures to calculate the revenue by allocation:
Profit cost center 1
Profit cost center 2
Profit cost center 3
These measures provide the annual revenue by allocation by customer.
I then created a virtual table (Join profit center) to get the list of all profit centers. This is where I am starting to struggle because I am unable to remove duplicates.
I then wanted to visualize a table to compare this table with the created measure but I am not sure how to combine the calculated measures. In order to have the revenue by profit center.
Any thoughts?
I have attached the pbix file.
I feel that I need to practice a lot to be comfortable in such scenarios.
It works well when the profit center is used only 1 time but I think there is something wrong when the profit center is used multiple times. Total of 6’832.75 does not tie to the annual revenue of 7’870. Shouldn’t we use an iterative function in the “profit” measure?
Also, will this measure work if we were to use a different context. If we had different currencies by customer, will that work as well or should we add an additional filter into the measure?
Let me digest this. Agreed, it seems to work for the pbix I sent, but for some reason I cannot understand yet, it does not seem to work in my real model. I am not familiar with this “Isinscope” function and need to understand it deeper. Let me keep this post open fore the moment before to click on solution.
It seems that your new measure calculates every scenario even if it does not exist (I am refering to the profit center 2, 3 and 4 which are displayed even if they are blank)
So if I change the context by adding currency or others fields, the measure will get a value for every customer and every currency even if blank. I will need to add a filter to remove blank figures otherwise I will huge calculated data which is unnecessary.
The ISINSCOPE function return true when your are in the hierarchy of the columns name you give.
So it will return true when you are in the rows of your table and false for the total.
If your model is in Direct Query, it’s possible that my measure doesn’t work because of ISINSCOPE…
Can you please replace ISINSCOPE with HASONEVALUE and tell me if this time it is working on your model ?
For the profit center 2, 3 and 4 you have a value because in your data, they have an annual revenue of 0 (and not BLANK). If the value is blank, you will not see them on the table.
Hi @JayLocher, we’ve noticed that no response has been received from you since September 18.
We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
