Forum Discussion

Frequent Visitor

8 years ago

Dynamic Column Variance Based on Year Selected with Slicer

I am relatively new to Power BI. I have a table that brings back the count of the number of controls tested per year based on the years selected in the slicer. I want to create a variance column next...

Icon for Community Support rank

Community Support

8 years ago

Create a measure use DAX like below and try again:

Measure =
CALCULATE ( aggregation, ALLSELECTED ( table[Year] ) )

Regards,

Jimmy Tao

Frequent Visitor

8 years ago

Thank you for your response. I tried the fix but it did not work.

v-yuta-msft
Community Support
8 years ago
Hi Joe6654,

Use a table chart instead and put the measure in the chart and try again.

Jimmy Tao
- Joe6654
  Frequent Visitor
  8 years ago
  Jimmy,
  
  Thanks for your response. When I switch to the table chart I still get an error.
PBIdashboards
Post Patron
3 months ago
The ALLSELECTED approach is the right direction but the measure needs to know which two specific years were selected to calculate the difference. Here's a cleaner pattern:

Variance =
VAR _years = ALLSELECTED('Table'[Year])
VAR _max = MAXX(_years, [Year])
VAR _min = MINX(_years, [Year])
VAR _curr = CALCULATE([Controls Tested], 'Table'[Year] = _max)
VAR _prev = CALCULATE([Controls Tested], 'Table'[Year] = _min)
RETURN _curr - _prev
Note this assumes exactly 2 years selected in slicer add a COUNTROWS guard if users can select more.
If you need this kind of dynamic comparison across multiple metrics regularly, Flexa Tables (AppSource) does it without DAX the variance column is a one-click button in the published report.