Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Hi,
You can count the return return result of measures by using CALCULATE(), COUNTX(), COUNTAX().
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |