Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |