Forum Discussion
Helper IIIAverage Cost per Day with conditional formatting
Hey,
I created an measure for average cost per day, which is shown in a monthly table. In this table I want to use conditional formatting to mark if the previous month is higher, lower or the same. I did the same with SUM and it works fine, but for the average values it doesn't work at some places. Here is the current final table:
As you can see in this example the highlighted yellow value is lower than the previous but it's marked as red (which means higher than the previous).
Average Cost Measure:
AvgCost = AVERAGEX(VALUES('DateDimension'[Date]), [Sum_Cost])Conditional formatting is based on the following conditions:
IconAzAvg Measure:
IconAzAvg =
VAR _val =
CALCULATE ( AVERAGE ( 'Cost'[Cost] ) )
-
CALCULATE ( AVERAGE ( 'Cost'[Cost] ),
PREVIOUSMONTH ( 'DateDimension'[Date] ) )
RETURN
SWITCH ( TRUE (),
_val < 0, -1,
_val = 0, 0,
_val > 0, 1 )Is there something wrong with the calculation?
Got it working with the following Measure:
IconAzAvg = VAR _val = CALCULATE ( AVERAGEX(VALUES('DateDimension'[Date]), [Sum_Cost]) ) - CALCULATE ( AVERAGEX(VALUES('DateDimension'[Date]), [Sum_Cost]), PREVIOUSMONTH ( 'DateDimension'[Date] ) ) RETURN SWITCH ( TRUE (), _val < 0, -1, _val = 0, 0, _val > 0, 1 )
3 Replies
- amitchandak
Super User
lkshck , Check if the previousmonth is working for you. There can few reason it is not working
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4\Also, try this measure in conditional formatting using the field value option
IconAzAvg =
VAR _val =
CALCULATE ( AVERAGE ( 'Cost'[Cost] ) )
-
CALCULATE ( AVERAGE ( 'Cost'[Cost] ),
PREVIOUSMONTH ( 'DateDimension'[Date] ) )
RETURN
SWITCH ( TRUE (),
_val < 0, "red",
_val = 0, "yellow",
_val > 0, "green" )PowerBI Abstract Thesis: How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
lkshckPREVIOUSMONTH is already used in the measure. I tried it with the field value option but still the same issues. Some are correct but some formattings are wrong.
- lkshck
Got it working with the following Measure:
IconAzAvg = VAR _val = CALCULATE ( AVERAGEX(VALUES('DateDimension'[Date]), [Sum_Cost]) ) - CALCULATE ( AVERAGEX(VALUES('DateDimension'[Date]), [Sum_Cost]), PREVIOUSMONTH ( 'DateDimension'[Date] ) ) RETURN SWITCH ( TRUE (), _val < 0, -1, _val = 0, 0, _val > 0, 1 )
