cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sanchit_1610
Helper I
Helper I

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

 

sanchit_1610_0-1620978694102.png

 

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 

sanchit_1610_1-1620979020910.png

 

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

 

sanchit_1610_2-1620980525888.png

 

Please advise me on this problem statement.

 

Thanks

Sanchit

 

 

1 ACCEPTED SOLUTION

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
V-lianl-msft
Community Support
Community Support

Hi @sanchit_1610 ,

 

Please refer to the sample:

V-lianl-msft_0-1621405823345.png

 

 

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

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

sanchit_1610
Helper I
Helper I

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Thanks @Ashish_Mathur , It did work for me.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User III
Super User III

Hi,

Share some data and please show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors