Counting Measures

[Anonymous]

I'm trying to count the measures

	Company A	Company B	Company C	Company D
PRODUCT W	$100	$120	$140	$150
PRODUCT X	$200	$180	$160	$140
PRODUCT Y	$150	$200	$160	$130
PRODUCT Z	$160	$120	$180	$140

If company  has lower price for the product it will sell. so I ranked them using the formula, where for product W, Company A-rank1, Company B-rank2, Company C-rank3, Company D-rank4. where rank 1 company gets to sell that product.

so i'm using what-if scneario variable to apply discounts to Company A such as applying 5% or 10% to all Company A products and trying to see how many products Company A will get to be rank1 or rank2 and etc.

so i used measure to apply discounts  from what-if scenario variables. and got the new ranks as well.

now my problem is counting how many each company has rank 1s.

counting in Powerbi shows that it can count columns not measures.

i tried setting calculated columns = measure but columns are not dynamic as i change the discount variable.

Need help

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Create measures like this.

__Current_value = MAX(Sheet6[Price])

__Rank = 
RANKX(
    FILTER(
        ALL(Sheet6),
        Sheet6[Product] = MAX(Sheet6[Product])
    ),
    [__Current_value],
     , ASC, Dense
)

__Count = 
COUNTX(
    FILTER(
        ALL(Sheet6),
        [__Rank] = 1 && Sheet6[Company] = MAX(Sheet6[Company])
    ),
    [__Rank]
)

Best regards,
Lionel Chen

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

[Ashish_Mathur]

Hi,

You can count the return return result of measures by using CALCULATE(), COUNTX(), COUNTAX().

[Greg_Deckler]

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.