Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hello Guys,
I have a table like
item | res | a |
1 | a | 30.98 |
1 | b | 30.98 |
1 | c | 20.34 |
1 | d | 17.7 |
and
my required table is like
item | res | a | max | 2nd | gap |
1 | a | 30.98 | 30.98 | 30.98 | 0 |
1 | b | 30.98 | 30.98 | 30.98 | 0 |
1 | c | 20.34 | 30.98 | 30.98 | 0 |
1 | d | 17.7 | 30.98 | 30.98 | 0 |
when i calculated its hard to detect max cause both a and b have same values so the coming 2nd max result is wrong
i.e the coming result is like
item | res | a | max | 2nd | gap |
1 | a | 30.98 | 30.98 | 20.34 | 10.64 |
1 | b | 30.98 | 30.98 | 20.34 | 10.64 |
1 | c | 20.34 | 30.98 | 20.34 | 10.64 |
1 | d | 17.7 | 30.98 | 20.34 | 10.64 |
Thanks & Regrads...
Solved! Go to Solution.
Hello I've finally figures it out
Here's the used related DAX
Hello I've finally figures it out
Here's the used related DAX
Hello @Ahmedx thanks for responding but
my required result table should like
item | res | a | max | 2nd | gap |
1 | a | 30.98 | 30.98 | 30.98 | 0 |
1 | b | 30.98 | 30.98 | 30.98 | 0 |
1 | c | 20.34 | 30.98 | 30.98 | 0 |
1 | d | 17.7 | 30.98 | 30.98 | 0 |
@BIswajit_Das Try:
max a 2nd =
VAR __Item = MAX('Table'[item])
VAR __Table = FILTER(ALL('Table'), [item] = __Item)
VAR __Summarized = SUMMARIZE(__Table, [item], [a], "__Count", COUNTROWS('Table'))
VAR __Max = MAXX(__Table, [a])
VAR __Result =
IF(
MAXX(FILTER(__Summarized, [a] = __Max),[__Count]) > 1,
__Max,
MAXX(FILTER(__Summarized, [a] < __Max), [a])
)
RETURN
__Result
Hello @Greg_Deckler Thanks for responding but what if i have multiple Items
i.e
item | res | a | max | 2nd | gap |
1 | a | 30.98 | 30.98 | 30.98 | 0 |
1 | b | 30.98 | 30.98 | 30.98 | 0 |
1 | c | 20.34 | 30.98 | 30.98 | 0 |
1 | d | 17.7 | 30.98 | 30.98 | 0 |
2 | a | 55 | 55 | 45 | 10 |
2 | b | 45 | 55 | 45 | 10 |
3 | a | 35 | 65 | 35 | 30 |
3 | c | 65 | 65 | 35 | 30 |
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.