Sum total from MINX calculation mistake

[nontmn]

Hello everyone,

I would like to find min value from many column

I make a Measure as

• M7 =
• VAR ValueTable =
• UNION(
• ROW("valuex",SUM('GR vs GI'[Order no GI])),
• ROW("valuex",SUM('GR vs GI'[Forecast])),
• ROW("valuex",SUM('GR vs GI'[Supply]))
• )
• RETURN
• MINX(ValueTable,IF(MIN([valuex])=0,0,[Valuex]))

but Total result is mistake for M7 total is should 333

and when I change from

• MINX(ValueTable,IF(MIN([valuex])=0,0,[Valuex]))

to

SUMX('GR vs GI',MINX(ValueTable,IF(MIN([valuex])=0,0,[Valuex])))

the result is

even I change to

• SUMX(DISTINCT('GR vs GI'[Material]),MINX(ValueTable,IF(MIN([valuex])=0,0,[Valuex])))

or

• SUMX(VALUE('GR vs GI'[Material]),MINX(ValueTable,IF(MIN([valuex])=0,0,[Valuex])))

Result still same.

I do not use column calculation because I have to compare with another measuer of dynamic sum column .

So how should I do? to correct sum total

[parry2k]

@nontmn and you didn't follow my measure, why you have var outside sumx, there is a reason why these are inside the sumx? Why you wouldn't just copy what I sent? Can you please follow the instruction before making any changes?

✨ Follow us on LinkedIn and subscribe to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[nontmn]

Sorry, I don't know difference between make var inside and outside formula.

[parry2k · Accepted solution]

@nontmn here is your measure

M7 = 
SUMX ( VALUES ('Table'[M] ),  //material column
VAR __sumO = CALCULATE ( SUM ('Table'[O] ) ) //order no gi columns
VAR __sumF = CALCULATE ( SUM ('Table'[F] ) ) //forecast column
VAR __sumS = CALCULATE ( SUM ('Table'[S] ) ) //supply column
RETURN
 MIN ( MIN ( __sumO, __sumF ), __sumS ) )

output

✨ Follow us on LinkedIn and subscribe to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[nontmn]

@parry2k 

output is 2499

[parry2k]

@nontmn not when I shared my screenshot, it is giving the correct total. In the screenshot you are sharing, are you showing the sum of each column or no aggregation?

[amitchandak]

@nontmn , Try like

M7 =
VAR ValueTable =
UNION(
summarize(Table, Table[Material],"valuex",SUM('GR vs GI'[Order no GI])),
summarize(Table, Table[Material],,SUM('GR vs GI'[Forecast])),
summarize(Table, Table[Material],SUM('GR vs GI'[Supply]))
)
RETURN
MINX(values(ValueTable[Material]),IF(MIN([valuex])=0,0,[Valuex]))

[nontmn]

@amitchandak  Sorry, I can't make last line exist

if I use 

MINX(VALUES(ValueTable),IF(MIN([valuex])=0,0,[Valuex]))

it error. and ValueTable is variable I don't know meaning of ValueTable[Material]

Result

Please tell me more, Thank you

[amitchandak]

@nontmn , sorry my mistake, try like

MINX(VALUES(ValueTable[Material]),IF(MIN([valuex])=0,0,[Valuex]))

[nontmn]

@amitchandak 

ValueTable is variable so it not have ValueTable[Material]

The main table is 'GR vs GI' 

[amitchandak]

@nontmn , Let us try a simple measure

measure =
var _1 =SUM('GR vs GI'[Order no GI])
var _2 = SUM('GR vs GI'[Forecast])
var _3= SUM('GR vs GI'[Supply])
return
MINX(values(ValueTable[Material]), MIN(MIN(_1,_2),_3))

[nontmn]

@amitchandak 

I use material from main table instead