Total of a Measure - Matrix

[rarcosta]

Im facing the following problem and Im not able to solve. If anyone could help me, I would appreciate it very much!

Power BI matrix:

Equipment	First	Last	Difference
Truck			54
Truck A	1	2	1
Truck B	20	23	3
Truck C	50	55	5
Total			54

First is a measure given by de minimum value of a column.

Last is a measure given bt th maximum value of the same column.

Difference is another measure given by Last - First.

I tried to use switch and isinscope, by I couldnt make the lines "Truck" and "Total" sum the results of the lines "Truck A", "Truck B" and "Truck C" in the column "Difference" (9).

Magic Measure =

SWITCH(

TRUE(),

ISINSCOPE('Table'[Equipment Type],[Difference],

ISINSCOPE('Table'[Equipment Name]),???????)

)

[stevedep · Accepted solution]

Hi,

Its more simple:

Diff = 
var tbl = SUMMARIZE('table';'table'[Type];'table'[Name];"min"; MIN('table'[Value]);"max"; max('table'[Value]))
return
CALCULATE(SUMX(tbl;CALCULATE(max('table'[Value]))-CALCULATE(min('table'[Value]))))

 

Power BI file is here.

Please mark as solution if this works for you. Thanks!

Kind regards,

Steve. 

[rarcosta]

That's it! Thank you!!

[Anonymous]

Hi,

Please see the solution on attached file.

If this post helps, then please consider Accepting it as the solution.

Thanks

Adi

[rarcosta]

Hi!

It's not possible to do like this, because both measures (First and Last) are calculated using values of the same column, like this:

Type	Name	Value
Truck	Truck A	1
Truck	Truck A	2
Truck	Truck B	20
Truck	Truck B	21
Truck	Truck B	22
Truck	Truck B	23
Truck	Truck C	50
Truck	Truck C	51
Truck	Truck C	52
Truck	Truck C	53
Truck	Truck C	54
Truck	Truck C	55

First = MIN('table'[Value])

Last = MAX('tabel'[Value])

Difference = Last - First

So i cant calculate the Difference in the data table like you suggested.