Forum Discussion

TejasShah's avatar
TejasShah
Icon for Helper I rankHelper I
2 years ago
Solved

Add a measure in Visual Table to add column

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
 
Need Help
Show and Tell
Tips and Tricks
  • Dangar332's avatar
    Dangar332
    2 years ago

    hi, TejasShah 

     

    avg measure 

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

     

    diff measure

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

     

     

10 Replies

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    hi, TejasShah 

     

    avg measure 

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

     

    diff measure

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

     

     

    • TejasShah's avatar
      TejasShah
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        TejasShah
        Icon for Helper I rankHelper I
        2 years ago

        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 ?

         

  • Vallirajap's avatar
    Vallirajap
    Icon for Resolver III rankResolver III
    2 years ago

    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's avatar
      TejasShah
      Icon for Helper I rankHelper I
      2 years ago

      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