How to calculate percentage using filters in DAX in Power BI?

user_14283168 0
Viewed 447

I have two columns

CustomerCode | Segmentation

AU656 | abc
AU765 | cdf
AU563 | abc
AU235 | abc
AU324 | opr
AU908 | opr
AU123 | pqr
AU234 |pqr

I have to find a distinct count of CustomerCode where segmentation is "abc" and "cdf" and "pqr" and divide it by the total number of CustomerCodes (all).

I created a measure -

#RSP =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[CustomerCode] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Segmentation] = "abc" ),
    'Table'[Segmentation] = "cdf",
    'Table'[Segmentation] = "opr"
)

However, this shows no value. Am I using the filters wrong? How do I calculate this?

dax powerbi-desktop powerbi
2 Answers

Your measure fails because Segmentation cannot be multiple values simultaneously. Try this instead:

#RSP =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[CustomerCode] ),
    'Table'[Segmentation] IN { "abc", "cdf", "opr" }
)

Ratio = DIVIDE ( [#RSP], DISTINCTCOUNT ( 'Table'[CustomerCode] ) )
user_7652260

In your data You don't have a customer with that specific condition: segmentation is "abc" and "cdf" and "pqr" (Any of the rows doesn't match this). You should use IN (also you can use OR)

#RSP = 
CALCULATE(DISTINCTCOUNT('Table'[CustomerCode]),FILTER(ALL('Table'),'Table'[Segmentation] in ("abc","cdf","opr")))

IF you want to find customers, that have rows with more than one segmentation code:

#RSP_2 =
var __custSeg1 = filter('Table'[CustomerCode], TREATAS({"abc"}, 
'Table'[Segmentation])
var __custSeg2 = filter('Table'[CustomerCode], TREATAS({"cdf"}, 
'Table'[Segmentation])
var __custSeg2 = filter('Table'[CustomerCode], TREATAS({"opr"}, 
'Table'[Segmentation])

return
calculate(DISTINCTCOUNT('Table'[CustomerCode]), __custSeg1 ,__custSeg2 
,__custSeg2 )
user_143665380
Related