# Combining calculated measures

Hi there,

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.

This is how my initial table look like :

.

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.

Thanks

Combined rows example.pbix (48.7 KB)

Hi,

If i get your question right you want to achieve the below result:

Let me know if its correct or i miss some thing.
Here is the pbix file:
Combined rows example.pbix (49.2 KB)

Thanks,
Anurag

Hi @Anurag ,

Thanks for considering my issue.

I think you got my question correctly.

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?

Thanks

Hi @JayLocher,

To have the result you want, Iâ€™ve changed a little the solution of @Anurag.

Iâ€™ve found small error in the profit measure, now each profit calculation is linked to the correct number.

Then I had to create another measure to calculate the total in your table.

Here is the final table results.

Yes, I think the measure will also work if you had another context like currencies.
Donâ€™t hesitate to come back to us if itâ€™s not the case

I hope my solution will help you.
Combined rows example.pbix (50.9 KB)

Best regards,
Joaly

4 Likes

Thank you @JBocher .

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.

Best regards
Jay

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.

Best regards,
Joaly

Hi @JayLocher , did the response provided by the other users help you solve your query?

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

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.

Hi,

Sorry for not responding earlier to this. I could not look at this the last 3 days. I will try to focus on this today.

Hi @JBocher

When I replace Isinscope by hasonevalue, it definitely helps as it removes all results ending up to zero.

There is still something wrong in my real model, but I believe this is coming from another measure that is not related to the ones in this resolution.

Your solution works well in the pbix I sent so I will tag this as solved.

Thank you very much for your assistance.

1 Like

Thank you @JayLocher for your answer.
I hope you will find the problem on your model.
Donâ€™t hesitate to come back to us if needed.

Please just tag my earlier response as a solution.
Thank you in advance.

Best regards,
Joaly