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.
Hey guys,
Can you please advise:
I have a table that looks like this and I would need to get the column in orange - the most frequent revenue value for Customer (disregarding blank values.)
Customer | Account | Revenue | Most Frequent Value |
A | aa | 150000 | 298700 |
A | bb | 298700 | 298700 |
A | cc | 0 | 298700 |
A | dd | 298700 | 298700 |
A | ee | 298700 | 298700 |
A | ff | 300 | 298700 |
B | F34 | (blank) | 250 |
B | T45 | 250 | 250 |
Hope this could be solved somehow?
Many thanks!
Solved! Go to Solution.
Hi @tomislav_mi ,
You can create two measures as below:
Count of revenue =
CALCULATE (
COUNT ( 'Revenue'[Revenue] ),
ALLEXCEPT ( 'Revenue', 'Revenue'[Customer], Revenue[Revenue] )
)
Most Frequent Value =
VAR _max =
MAXX ( ALLEXCEPT ( 'Revenue', 'Revenue'[Customer] ), [Count of revenue] )
RETURN
CALCULATE (
MAX ( 'Revenue'[Revenue] ),
FILTER (
ALL ( 'Revenue' ),
'Revenue'[Customer] = MAX ( 'Revenue'[Customer] )
&& [Count of revenue] = _max
)
)
Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
@tomislav_mi For most recent value there should be a date associated with it, do you have it? Otherwise a solution can still be prepared but it will break if the logic doesn't fits later.
Hey,
thank you for your response.
Not most recent but most frequent value so there is no date in this table.
Hi @tomislav_mi ,
You can create two measures as below:
Count of revenue =
CALCULATE (
COUNT ( 'Revenue'[Revenue] ),
ALLEXCEPT ( 'Revenue', 'Revenue'[Customer], Revenue[Revenue] )
)
Most Frequent Value =
VAR _max =
MAXX ( ALLEXCEPT ( 'Revenue', 'Revenue'[Customer] ), [Count of revenue] )
RETURN
CALCULATE (
MAX ( 'Revenue'[Revenue] ),
FILTER (
ALL ( 'Revenue' ),
'Revenue'[Customer] = MAX ( 'Revenue'[Customer] )
&& [Count of revenue] = _max
)
)
Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
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 |
---|---|
96 | |
95 | |
80 | |
74 | |
66 |
User | Count |
---|---|
130 | |
106 | |
105 | |
86 | |
72 |