Dynamic Column calculation based on Slicer Selection

[thameem10]

I have 3 Tables "Variables", "Sales Data" and "Scrap Ratio".

The Variable Table holds the year from 2009 to 2025. Scrap Ratio has the Age Number wise Category description and Scrappage rate and sales data has the following data points (sample data below with excel formulas and pivot).

Sold Month	Business Division	Sales Group	Model Group	Count	Age	Age Bucket	Remain Rate	Active Units
2009	Truck	Retail	MDL1	803	99	>10	0	0
2010	Truck	Retail	MDL1	752	99	>10	0	0
2011	Truck	Retail	MDL1	735	99	>10	0	0
2012	Truck	Retail	MDL1	949	99	>10	0	0
2013	Truck	Retail	MDL1	1680	99	>10	0	0
2014	Truck	Retail	MDL1	1685	10	N-9	0.661	1114
2015	Truck	Retail	MDL1	2795	9	N-8	0.683	1909
2016	Truck	Retail	MDL1	2221	8	N-7	0.719	1597
2017	Truck	Retail	MDL1	2085	7	N-6	0.76	1585
2018	Truck	Retail	MDL1	1850	6	N-5	0.803	1486
2019	Truck	Retail	MDL1	1059	5	N-4	0.852	902
2020	Truck	Retail	MDL1	814	4	N-3	0.907	738
2021	Truck	Retail	MDL1	823	3	N-2	0.96	790
2022	Truck	Retail	MDL1	1768	2	N-1	0.985	1741
2023	Truck	Retail	MDL1	1398	1	N	0.985	1377
2024	Truck	Retail	MDL1	2429	0	CY	1	2429

Excel Formulas :

Age: =IF(Variables!$B$1-'NV Data'!$A2>10,99,Variables!$B$1-'NV Data'!A2)

Age Bucket =IFERROR(XLOOKUP(F2,'Scrap Ratio'!A:A,'Scrap Ratio'!B:B),">10")

Remaining Rate =IFERROR(XLOOKUP(F2,'Scrap Ratio'!A:A,'Scrap Ratio'!C:C),0)

The Age column in the "Sales Data" table should reflect the Age difference between the Selected / Filtered year and Sold Month. This should dynamically change upon using the slicer and the overall Pivot / Power BI Matrix should change accordingly.

Scrap Ratio Table

Age No	Age N	Remain Rate
0	CY	1
1	N	0.985
2	N-1	0.985
3	N-2	0.96
4	N-3	0.907
5	N-4	0.852
6	N-5	0.803
7	N-6	0.76
8	N-7	0.719
9	N-8	0.683
10	N-9	0.661

I want to replicate this excersise in Power BI, where the Power BI Matrix has to reflect the pivot structure for the selected / filtered year in the Slicer (Table - Variable).

In this case 2023 is selected in the Slicer, basis which "Age" column calculated and then the this pivot got refreshed.

I would apriciate your support in resolving this one.

@amitchandak @Ashish_Mathur 

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

[thameem10]

Hi Ashish, Thanks for your reply. Please have this file link for your understanding of the formulaes.

Active Units by Selected Year.xlsx

The Idea is to see the pivot data Active Units basis the selected year.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

[thameem10 · Accepted solution]

Hi Ashish, Thanks a lot for solving this in Power BI. I apriciate your expertise in this space.

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Not sure how much i can help but i would like to try.  Share the download link of the MS Excel file with your formulas/Pivot Tables already there.  It will be easier to understand your logic from there and convert it into measures.