Calculating the percentage of grand total by category with DAX for one category only

Question

I need help calculating the percentage of each category in a column (based on the grand total) in DAX but for one specific category.

This is how the data is structured. Each row is an individual transaction with an ID column and item column.

I need to get the % of transactions that are for bagels only. This is my sql code I currently use.

`Select 100 - CAST(count([Items]) 
 - count(case [Items] when 'Bagel' then 1 else null end) AS FLOAT) 
 / count([Items]) * 100 as Percent_Bagel 
 from Items_Table where Items != 'Coffee' 
 and Items != 'Muffin'`

I need this to be converted to a DAX formula to use in a Power BI measure if possible, but I am new to DAX and don't know where to begin.

Thank you.

Vitor Monteiro Vitor Monteiro · Accepted Answer · 2020-09-01T18:00:59

The "right" implementation for you always depends on the context. You can achieve your goal through different approaches.

I have used the following:

Measure = 
DIVIDE(
    -- Numerator: Filter all Bagel's transaction and count them
    CALCULATE(
        COUNT('Table'[Transaction ID]),
        FILTER('Table', 'Table'[Item] = "Bagel")
    ),
    -- Denominator: Remove any filter - essentially fixing the full table - and count all transactions we have
    CALCULATE(
        COUNT('Table'[Transaction ID]), 
        ALL('Table'[Item])
    ),
    -- If something goes wrong with the DIVIDE, go for 0
    0
)

You may also use the filters to rule out all measures that are not blank.

Without measure filter

With measure filter (Other categories are gone)

Hope this is what you are looking for!

Calculating the percentage of grand total by category with DAX for one category only

1 Answers