Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a problem of getting the percentage on customer sales in matrix table thta is sliced by store_id and employee_id using ALLEXCEPT function. The percentage of customer sales need to obrain from dividing customer sales measure in current filter by the same measure of the respective store_id, instead of total customer sales of all the store_id.
Here is my expectation of the output and the DAX when using ALL function on the staff_id.
% of Customer Sales =
VAR ALLExceptSales =
CALCULATE(
[Customer Sales],
ALL(
'Employee Lookup'[staff_id]
)
)
VAR Ratio =
DIVIDE(
[Customer Sales],
ALLExceptSales,
BLANK()
)
RETURN Ratio
Hi,
According to your description, I can’t roughly understand what’s your true problem is, for more info about the ALLEXCEPT() function, you can refer to this link:
https://docs.microsoft.com/en-us/dax/allexcept-function-dax
What’ more, I think the pbix file you uploaded has some sensitive data of your company, I suggest you to delete it immediately and post a test pbix file without sensitive data if it’s possible.
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Can anyone help me?
User | Count |
---|---|
56 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |