distinctcount of product with lowest price (all) for each distributor

[xuexi1890]

Hello,

below is my dataset (as an example), I want to calculate how many products with lowest price sold of each distributor, same as SummaryTable1, meanwhile having another SummaryTable2 to show me what are the cases.

thank you so much for helping me out!

my initial attempt was to create an array of numbers (each product code with min price) and then use this array as a criterion to filter in my dataset when the product is the same and the price is equal to the min price, but of course failed. I don't quite understand the concept of PBI when the array is relevant to a certain column (same product).

Sincerely,
Nate

[v-chuncz-msft]

@xuexi1890 

You may add a calculated column first.

Column =
IF (
    'Table'[Price]
        = MINX (
            FILTER ( 'Table', 'Table'[Product] = EARLIER ( 'Table'[Product] ) ),
            'Table'[Price]
        ),
    1
)

[xuexi1890 · Accepted solution]

@v-chuncz-msft 

thanks Sam. i tried myself and succeeded.

i put it here if anyone is interested.

MinPrice =

VAR _Quarter=ALLSELECTED(Table1[Quarter])

VAR _Month=ALLSELECTED(Table1[Month])

Return

CALCULATE(

min(Table1[price]),

ALL(Table1),

values(Table1[Product]),

Table1[Quarter] IN _Quarter,

Table1[Month] IN _Month

)

DistinctCount of CP =

VAR _Quarter=ALLSELECTED(Table1[Quarter])

VAR _Month=ALLSELECTED(Table1[Month])

Return

CALCULATE(

DISTINCTCOUNT(Table1[Product]),

FILTER(ALLEXCEPT(Table1,Table1[Quarter],Table1[Month]),Table1[price]=[MinPrice]),

VALUES(Table1[Distributor]),

Values(Table1[Product]),

Table1[Quarter] IN _Quarter)