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.
Best Answer
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 themCALCULATE(COUNT('Table'[Transaction ID]),FILTER('Table', 'Table'[Item] = "Bagel")),-- Denominator: Remove any filter - essentially fixing the full table - and count all transactions we haveCALCULATE(COUNT('Table'[Transaction ID]), ALL('Table'[Item])),-- If something goes wrong with the DIVIDE, go for 00)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!