Forum Discussion
Subtract the largest value from a column
Hello,
For a certain requirement, I am trying to compare the total result from Sales to Expected Sales by subtracting the best selling location from a certain Region.. Here is how my data looks in one table.
Table 1 has the specifics by region.
Region Location Sales
A A_1 50
A A_2 80
A A_3 70
I want to show this data as...
Region Sales Expected Sales
A 120 75
Goal: To remove the HIGHEST performing location's value from each Region, and see if it still exceeds the Expected Sales column.
In this case I would want Region A to perform the function 50 + 70 (exclude 80) = 120. 120 > 75, so PASS.
Is there some sort of function I can write that says if Route = the same Route, find and subtract the greatest value?
6 Replies
- parry2k
Super User
Anonymous where do you get the value of 75 for expected sales?
- amitchandak
Anonymous , Try like
Measure =
var _max = maxx(filter(allselected(Table) , Table[Region] =max(Table[Region]) ), [sales])
return
calculate(sum(Table[Sales]), filter(Table, Table[Sales] >_max) )
- Jihwan_Kim
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
Sales except the highest: = VAR highest = MAX ( Sales[Sales] ) RETURN SUMX ( FILTER ( Sales, Sales[Sales] <> highest ), Sales[Sales] )Expected sales: = SUM( Region[Expected Sales] )Status: = IF ( [Sales except the highest:] > [Expected sales:], "Pass", "Check" )
