Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to calculate Multiple data correctly

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 NameMarketProduct SalesMarket Sales
PR_AMarket A     5150
PR_BMarket A     7150
PR_CMarket B     15252
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    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.⚡

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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.