cancel
Showing results for
Did you mean:
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

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

Thanks

Sanchit

1 ACCEPTED SOLUTION
Super User III

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
9 REPLIES 9
Community Support

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

Helper I

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

Thanks

Sanchit

Helper I

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

Super User III

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
Helper I

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

Thanks

Sanchit

Super User III

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Thanks @Ashish_Mathur , It did work for me.

Super User III

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User III

Hi,

Share some data and please show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

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

#### 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

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