How to calculate Multiple data correctly

[Anonymous]

Hi there,

I have a problem that I don't know how to google-it . Here is my problem ,

I have lots of product and I compare them with Rivals. I have a table like below and Some of my product which competitive in the same market has same value on Markes Sales Column.

When I try to calculate Market Index  with this formula (Product Sales / Market Sales ), formula doesn't get to correct value. 

For Example;
I want for PR_A --> 5 / 150

But measue calculated --> 5 / 300

How Can I make a correct formula if there is multiple value in table ? 

Product Name	Market	Product Sales	Market Sales
PR_A	Market A	5	150
PR_B	Market A	7	150
PR_C	Market B	15	252

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Try to create a measure like this:

Measure = 
DIVIDE(MAX('Table'[Product Sales]),MAX('Table'[Market Sales]))
//MAX('Table'[Product Sales])&"/"&MAX('Table'[Market Sales])

Result:

Please refer to the attachment below for details. Hope this helps.

If it doesn't work,  could you please consdier sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.

It makes it easier to give you a solution.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous maybe add a flag to break the tie between own/competitor product, maybe easier will be to add a index column in the power query and then show this new column in the visual.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡