Measures for all SKU and SUM

[laba031095]

I have a sales table where there is customer name, sales amount and SKU. What measure should I prescribe so that when choosing SKU and the amount, I can see only those customers who have sold for the required amount and have sold all the selected SKU? I tried to prescribe the measure myself.

Stores Selling All SKUs = COUNTROWS(FILTER(DISTINCT(Table[Store]), CALCULATE(DISTINCTCOUNT(Table[SKU])) = FILTER(Table2[SKU[))

[speedramps]

"Sold all the SKUs" does not makes sense.  

Please make an proper effort to describe your problems with lots of examples if you want helping quickly.
We are not paid, we are volunter helpers. Thank you.

[laba031095]

Good. Sorry, I just don't speak English well.

I have a table Sales

Example

Shop1 Product 1
Shop1 Product 2
Shop1 Product 3
Shop 2 Product 1
Shop 3 Product 1
Shop 3 Product 2
Shop 3 Product 3
Shop Product 4
I want to do measure
If i Select Product 1 Product 2 and Product 3

I see in table

Shop 1
Shop 3

If i select Product 1, i see

Shop 1
Shop 2
Shop 3

Thanks

[v-stephen-msft · Accepted solution]

Hi @laba031095 ,

Very clear example shows. 

Here's my soltuion.

1.Create a measure

Measure = var _count1=CALCULATE(DISTINCTCOUNT('Table'[Product]),ALLSELECTED('Table'))
var _count2=CALCULATE(COUNT('Table'[Shop]),FILTER(ALLSELECTED('Table'),[Shop]=MAX('Table'[Shop])))
return IF(ISFILTERED('Table'[Product]),IF(_count1=_count2,1),1)

2.Put the measure into Filters, and set show items when the value is 1.

3.Results is as follows.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[speedramps]

I dont undersrtand  "When choosing SKU and the amount, I can see only those customers who have sold for the required amount and have sold all the selected SKU".  The sentance doeas not make sense.

Stores who sold required amount =
/// this measure assumes you have selected one sku and one amount

// get the selected amount
VAR myamount = SELCETED(Table[amount])

// create a subset for any
VAR mysubset = FILTER(
All(Table[amount]),
All(Table[amount]) >= myamount

RETURN
// how many stores in the subset ?
CALCULATE(
DISTINCT(Table[Store]),
mysubset
)

[laba031095]

I need to display those who sold all the SKUs that I selected using the filter. I have a SKU filter on my sheet.

[negi007]

@laba031095 i understand you only need the count of customers who has sold all skus

in this case, you can follow the below approach

1. first measure to calculate all unique SKUs

SKUs_Total = calculate(DISTINCTCOUNT(sales_table[sku_id]),ALL(sales_table))

2. second measure to calculate actual SKUs sold by customer

SKUs_sold = calculate(DISTINCTCOUNT(sales_table[sku_id]),'Table'[Volume]>0)

3. third SKUs to count all unique customers who sold all SKUs

Store_selling_all_SKUs = CALCULATE(DISTINCTCOUNT(sales_table[cust_id]),filter(sales_table,sales_table[SKUs_sold]>=sales_table[SKUs_Total]))

we could have also created one single measure but for simplicity and explantation 3 different measures were created. You can combine them to create one single measure as well.

i hope it helps to solve your problem. thanks

[laba031095]

No, I need to display those who sold all the SKUs that I selected using the filter. I have a SKU filter on my sheet.