CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
coder_bg
coder_bg

Reputation: 370

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

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?

Upvotes: 1

Views: 1285

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

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] ) )

Upvotes: 2

msta42a
msta42a

Reputation: 3741

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 )

Upvotes: 0

Related Questions