Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
Hi @sanchit_1610 ,
Please refer to the sample:
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
password -12345
Thanks
Sanchit
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.
Please use below link to access the excel, I have explained the case scenario and the result set what i want on the right.
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.
You are welcome.
Hi,
Share some data and please show the expected result.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |