Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have 2 tables as below :
where I have calculated Avg =
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
Solved! Go to Solution.
hi, @TejasShah
avg measure
avg =
CALCULATE(
AVERAGEX( consumption,consumption[consumption]),
ALLEXCEPT(consumption,consumption[meter name])
)
diff measure
diff =
MIN(consumption[consumption])-[avg]
hi, @TejasShah
avg measure
avg =
CALCULATE(
AVERAGEX( consumption,consumption[consumption]),
ALLEXCEPT(consumption,consumption[meter name])
)
diff measure
diff =
MIN(consumption[consumption])-[avg]
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.
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 ?
hi, @TejasShah
clear specification
provide requirment
means describe specific unit for each id for help
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
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!
Hi, @TejasShah
If you want it hard-coded then replace (>=a) here a with your desired value like 105,107
Like below (>=105)
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
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
User | Count |
---|---|
86 | |
81 | |
68 | |
63 | |
54 |
User | Count |
---|---|
120 | |
99 | |
91 | |
83 | |
65 |