cancel
Showing results for
Did you mean:
Member

## Median of distinct customers

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, W/E 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

## Re: Median of distinct customers

Hi @po,

To create a measure as below. If it doesn't meet your requirement, kindly share your excepted result to me.

```Measure =
CALCULATE (
DISTINCTCOUNT ( Table1[Shop] ),
FILTER ( Table1, 'Table1'[target met] = 1 )
)
/ CALCULATE ( DISTINCTCOUNT ( Table1[Shop] ), ALL ( Table1 ) )
```

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
3 REPLIES 3
Community Support Team

## Re: Median of distinct customers

Hi @po,

To create a measure as below. If it doesn't meet your requirement, kindly share your excepted result to me.

```Measure =
CALCULATE (
DISTINCTCOUNT ( Table1[Shop] ),
FILTER ( Table1, 'Table1'[target met] = 1 )
)
/ CALCULATE ( DISTINCTCOUNT ( Table1[Shop] ), ALL ( Table1 ) )
```

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Member

## Re: Median of distinct customers

Hi

Thanks for reply and .pbix file

Member

## Re: Median of distinct customers

Hi,

Just a quick question.

If wish to do an average for the shop and the count of the ones where average = 0 how could we do this?

Can calculate the average

CALCULATE (
AVERAGE ( Table1[target met] ),
FILTER ( ALLSELECTED ( Table1 ), Table1[Shop] = MAX ( Table1[Shop] ) )
)
as below.
but would like to the do a distinct count of shops with an average = 1  i.e in this case retrive 2 shops (b and c)

Having an issue with this part.

Announcements