CODEWITHSUNDEEP
powerbidax
Azul
Azul

Reputation: 25

Problem on using ALLEXCEPT to get percentage

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.

output

% 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.

allexcept

all except dax

dax 2 table

dax2

Here is the pbix file for testing.

https://drive.google.com/file/d/1fRrfsikHl9aK06GzAozJ9Wc16Ue0YJm2/view?usp=sharing

Anyone can hint me on?

Upvotes: 0

Views: 745

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

Have you tried ALLSELECTED instead of ALLEXCEPT?

% of Customer Sales =
DIVIDE (
    [Customer Sales],
    CALCULATE ( [Customer Sales], ALLSELECTED ( 'Employee Lookup'[staff_id] ) )
)

Upvotes: 1

Ashok Anumula
Ashok Anumula

Reputation: 1515

Replace DAX with following

% of Customer Sales = 
VAR ALLExceptSales = 
CALCULATE(
    sum(Table[Customer Sales]),

ALLEXCEPT(Table, 'Table'[store_id])
   )

VAR Ratio = 
DIVIDE(
    sum(Table[Customer Sales]),
    ALLExceptSales,
    BLANK()
)

RETURN Ratio

Upvotes: 1

Related Questions