Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Partition Measure values into groups and change dynamically with other filters

Hi All,

 

Greetings!!!

 

I am trying to calculate the number of clients with percentage of orders placed falling under various category. Consider below scenario

 

 

From above table i have to calculate percentage of orders, below is the formula:

((Sum(Sub-Category A)+Sum(Sub-Category B)+Sum(Sub-Category C))/(Sum(Category A)+Sum(Category B)+Sum(Category C)))*100 

 

The above percentage must be calulated for each client id based on the filter selected, i.e. Date, client id, product. Below are the separate tables used in data model 

 

Now, based on the percentage we calculated above, if it falls under 25% then we count the distinct client id and want to populate it under "perc<25%" , if above 25% and below 50% then "perc<50%" and so on, refer below image when we select Product B1

 

 

Please advise me on this problem statement.

 

Thanks

Sanchit

 

 

Need Help
Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You may download my PBI file from here.  From the matrix visual, you may remove the Client ID, if you so wish.

    Hope this helps.

9 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi V-lianl-msft 

       

      Thanks for your response!

       

      I am not getting correct answer with the calculation, the client id is getting categorise across multiple category if we have more data, below is the data

       

      Attached file 

      https://easyupload.io/37u8xp

      password -12345

       

      Thanks

      Sanchit

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Ashish_Mathur ,

     

    I am not able to attach any files, but please find below table data (tab separated)

    Date Client Id Product Category A Category B Category C Sub-Category A Sub-Category B Sub-Category C
    5/12/2021 D121 A1 20 21 30 1 3 2
    4/12/2021 F122 B1 21 21 12 2 4 3
    3/12/2021 C123 C1 30 12 21 3 5 4
    2/12/2021 D121 B1 12 12 20 4 6 3
    1/12/2021 F122 C1 21 12 21 5 3 4
    12/12/2020 C123 A1 21 20 30 6 2 5
    11/12/2020 D121 C1 12 21 30 3 3 6
    10/12/2020 F122 A1 12 30 12 2 4 3
    9/12/2020 C123 B1 12 12 19 2 5 4

     

    Let me know if you need any other help.

     

    The result we just need to filter on product=B1 and then categorise the value based on the range

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      5 years ago

      Hi,

      Paste the data in an MS Excel file and share the download link of that Excel file.  In that file, please also show the expected result very clearly.  It will be very helpful if you can show the formulas in that file as well - i will be able to understand much better.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Ashish_Mathur 

         

        Please use below link to access the excel, I have explained the case scenario and the result set what i want on the right.

         

        https://easyupload.io/34hvsx 

         

        Password to download -12345

         

        Thanks

        Sanchit