Hi all,
I have a calculation that I had calculated on excel, but need to now write a measure to calculate it within Power BI.
Current situation:
Excel:
| Year | Spending | Spending per Year (excel calculation) | Average Spending across Years (excel calculation) | Conditional Flag (excel calculation) |
| 2020 | $5 | $14 | $14.25 | NA |
| 2020 | $6 | $14 | $14.25 | NA |
| 2020 | $3 | $14 | $14.25 | NA |
| 2021 | $7 | $17 | $14.25 | Flag2021 |
| 2021 | $10 | $17 | $14.25 | Flag2021 |
| 2022 | $7 | $7 | $14.25 | NA |
| 2023 | $8 | $19 | $14.25 | Flag2023 |
| 2023 | $11 | $19 | $14.25 | Flag2023 |
Power BI:
- Page slicer by date
- Gauge chart: Value based on the following formula (thank you @isjoycewangFilter for Chart Based on Filter for Page )
Column Chart Value =
var maxyear = MAXX(ALLSELECTED('Table'), 'Table'[Date].[Year])
var minyear = maxyear -2
var selectedyear = SELECTEDVALUE('Table'[Date].[Year])
var result =
IF( selectedyear <= maxyear && selectedyear >= minyear,
CALCULATE([MeasureX], ALL('Table'[Date]), 'Table'[Date].[Year] = selectedyear),
BLANK())
return result
- MeasureX: Calculates the flags less "NA"
MeasureX = Calculate(
Distinctcount(Table[Conditional Flag]),
Filter(Table,Table[Conditional Flag] <> "NA"
))
I would like to move the calculation of the Flagging to Power BI. This is because I would like the average to be calculated based on the page slicer and the 2 years before, i.e. if end-date of page slicer = 2023, then years for calculation = 2021, 2022, 2023
The current flagging flags 2 out of 4 years. I would like to have the following result:
If years = 2021, 2022, 2023, then 2021 and 2023 will be flagged with sum of the year more than the average of 14.3
If years = 2020, 2021, 2022 then 2020, 2021 will be flagged with the sum of the year more than the average of 12.66
Thank you!
