Help in creating Ratio Delta measure

[Ashwini_09]

Hi, I have the following dataset (image attached). I want to find the Ratio difference as a measure for each year based on the current position effective date column. For e.g. if the year filter is 2023, I want the calculation to be 0.76-0.67, similarly for year 2022 the calculation should be 0.74-0.67. I am facing issues in this logic because the when I filter by year I lose the data from other years. Could you please help me with some pointers on how to solve? I have tried multiple approaches based on my knowledge but can't reach to a solution.

[Anonymous · Accepted solution]

Hi @Ashwini_09 ,

I can confirm that if the field applied on the year slicer originate from the same table as the year field in the table visual, the table visual will indeed display only the corresponding data for the selected year, as you have correctly pointed out.

To accomplish your objective, you may follow the steps outlined below:

1. Create a Year Dimension Table: Initiate by creating a year dimension table. Please ensure that you do not create any relationship with your fact table at this stage.

2. Apply the Year field: Subsequently, apply the year field from the newly created Year dimension table to the year slicer, effectively replacing the original year field .

3. Create Measure: Finally, create the necessary measure to obtain the ratio difference.

Measure =
VAR _selyear =
    SELECTEDVALUE ( 'Years'[Year] )
VAR _curratio =
    CALCULATE (
        SUM ( 'Table'[Ratio] ),
        FILTER ( 'Table', 'Table'[Year] = _selyear )
    )
VAR _preratio =
    CALCULATE (
        SUM ( 'Table'[Ratio] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] = _selyear - 1 )
    )
RETURN
    _curratio - _preratio

If the above one can't help you figure out, please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards