Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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/

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
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.