Forum Discussion
Dynamic Column calculation based on Slicer Selection
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.
Hi,
PBI file attached.
Hi Ashish, Thanks a lot for solving this in Power BI. I apriciate your expertise in this space.
5 Replies
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.
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.
Hi,
PBI file attached.
Hi Ashish, Thanks a lot for solving this in Power BI. I apriciate your expertise in this space.