Forum Discussion

alvin199's avatar
alvin199
Icon for Helper III rankHelper III
4 years ago

Problem on using ALLEXCEPT to get percentage

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

 


I have tried using ALLEXCEPT on store_id from sales by store table, ALLEXCEPT store_id from store lookup table and ALLEXCEPT on both the tables, still giving me different output. 

Anyone can hint me on?

2 Replies

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.