cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
po Member
Member

Count of distinct customers where median is certain value?

Hi,

Wish to calculate the following

 Percentage of Shops which hit target which varies dynamically depending on w/edate and store name which selected in slicers

 Data

 Shop, Week Ending date, target met

 target met is derived in table and can set correctly at table level to 0 depeidning on store percentage above 70, target met is set to 1

 e.g

 

A, 05/01/2019,0

A,12/01/2019,0

A,19/01/2019,1

B, 05/01/2019,0

B,12/01/2019,1

B,19/01/2019,1

C, 05/01/2019,1

C,12/01/2019,1

C,19/01/2019,1 

i.e. in above if looked at all this median OF A is 0, b is 1 and c is 1 i.e. 66% met target i.e 2 out of 3 stores met target.

 

However if were just looking at 19/01/2019 all met target i.e. would be 100%. 

Tried

CALCULATE (
DISTINCTCOUNT ('Sales'[Store Number] ), FILTER('Sales',median('Sales'[target_met])=1))
but gives count of 3 rather than 2


How can we best achieve this in power bi?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Count of distinct customers where median is certain value?

Hi @po

 

You may create a measure with SUMMARIZE Function.

Count =
VAR _table =
    SUMMARIZE ( Sales, Sales[Store Number], "media", MEDIAN ( Sales[Target met] ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Sales[Store Number] ),
        FILTER ( _table, [media] = 1 )
    )

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Count of distinct customers where median is certain value?

Hi @po

 

You may create a measure with SUMMARIZE Function.

Count =
VAR _table =
    SUMMARIZE ( Sales, Sales[Store Number], "media", MEDIAN ( Sales[Target met] ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Sales[Store Number] ),
        FILTER ( _table, [media] = 1 )
    )

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
po Member
Member

Re: Count of distinct customers where median is certain value?

Thans for reply.