Add a measure in Visual Table to add column

[TejasShah]

I have 2 tables as below :

where I have calculated Avg = 

CALCULATE(AVERAGE(Consumption[Consumption]), FILTER(Consumption, (Meter_Consumption_Table[Year] = YEAR(NOW())) && Consumption[ID] = Master_Meter[ID]))

I am creating a Visual Table on the desktop appln. where in I am able to get below results:

But, I also need another column which would give me Difference of Consumption and Average:

I only need the Red values, green values can or cannot be included based on requirement. 

I tried doing this using the below formula, but it takes the Sum of all the Average values in the column average and compares it with the Consumption details instead of comparing it with individual ID average

Difference =

VAR day_consumption = SUM(Consumption[Consumption])

RETURN

IF (day_consumption > SUM(Master_Meter[Average]), day_consumption - SUM(Master_Meter[Average])

Can someone please help me with this problem

Powerbivisual

[Dangar332 · Accepted solution]

hi, @TejasShah 

avg measure 

avg = 
 CALCULATE(
    AVERAGEX( consumption,consumption[consumption]),
    ALLEXCEPT(consumption,consumption[meter name])
)

diff measure

diff = 
MIN(consumption[consumption])-[avg]

[TejasShah]

Hi @Dangar332 ,

Thank you so much for the quick response, really appreciated. 

I have achieved my solution exactly how I want it to be.

Thanks once again.

[TejasShah]

Sorry to bother you @Dangar332 ,

But if I need a filter where in I only need consumption which are above X units (where X is different for individual IDs), can we include the same while calculating the avg ?

[Dangar332]

hi, @TejasShah 

clear specification
provide requirment 

means describe specific unit for each id for help

[TejasShah]

hi @Dangar332, Sure, I mean to say for example as per the DAX formula given by you, we are successful in getting avg of all the values for a specific Meter_Name: avg = CALCULATE( AVERAGEX( consumption,consumption[consumption]), ALLEXCEPT(consumption,consumption[meter name]) ) But for instance, if I need to consider only values above 105 for meter_name C for calculating the avg: i.e. instead of 106.5 for "C's" avg, I'll get 109.5 as avg since only 107 and 112 would be considered

[Dangar332]

hi, @TejasShah 

try below measure 

Measure 2 = 
var a = SELECTEDVALUE(consumption[consumption])
RETURN
   
    CALCULATE(
    AVERAGE(consumption[consumption]),
    consumption[consumption]>=a,
   ALLEXCEPT(consumption,consumption[meter name])
    )

make slicer for consumption 

[TejasShah]

Hi @Dangar332 

Okay, I am able to get what I need through this as well, just that I have to add a slicer for particular meter name.

Thank you so much for your help. Have a wonderful day!

[Dangar332]

Hi, @TejasShah 

If you want it hard-coded then replace (>=a) here a with your desired value like 105,107

Like below  (>=105)

[Vallirajap]

Hi @TejasShah 

I tried the above use case based on my understanding; below, I share the screen shot. Is there any query you mentioned to ask?

Did I answer your question?

Mark my post as a solution, this will help others...!

Hit the kudo also,

Thank you

[TejasShah]

Hi @Vallirajap ,

You are using the same table for calculating the difference, actually this table which I have is a Visual table on my BI report and not a data table. I suppose we won't be able to use Visual table columns in measure to achieve our difference column