There are 3 ways I know how to search for the higher value for unique numbers. For the first one, I will use the “group by” function from the power query but before to start, take note that this method may modify the structure of the table. Let´s say that I have this table:
“inc01” and “inc04” are duplicate and one of percentage is higher than the other so to remove the lower one:
From here, there are 2 options, the left one will focus on “number” and “percentage” columns and for the other columns, it will group them in one single column. The right one will focus on all columns:
Left option | Right option |
Final result | |
NOTE: for the minimum value, select Min instead of Max
On the left option, only “number” and “percentage” columns are showing and the other ones have disappear (in my case, the “maxduplicate” column). On the right option, to keep all columns, I need to add an aggregation for each column (in my case, just need to click once).
The second method is to create a new table from the main table so click on “home -> new table”:
Put this formula:
SUMMARIZE('table','table'[argument1],"value2",MAX('table'[argument2]),"value3",MAX('table'[argument3]))
NOTE:
The last method is to use only the main table so I will add a new column:
With this formula:
CALCULATE(MAX('table'[argument1]),ALLEXCEPT('table','table'[argument2]))
NOTE: to add another criteria, in ALLEXCEPT, just add another argument. For instance:
ALLEXCEPT('table','table'[argument2],'table'[argument3])
If I want to remove the lower value, I will use this one:
IF(CALCULATE(MAX('table'[argument1]),ALLEXCEPT('table','table'[argument2]))='table'[argument1],
'table'[argument1],BLANK())
Now, let´s do some visuals with the last method because for the first and second one, there is no need for some extra explanation. On the left, using the default columns and on the right using the new ones but first, I will have to create a measure with this formula:
CALCULATE(COUNT('table'[argument1]),NOT('table'[argument2])=BLANK())
Left visual | Left build | Right visual | Right build |
This article Power BI: calculate the (high) value of unique numbers may interest you.
When you are managing a team, “how to be a good manager” is the “must”...
As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports...
ITIL V3 is going to be obsolete...
Managing an IT service when I start a new company is not an easy task, particularly true, if the service...