Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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...
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!