topic Dynamic Column calculation based on Slicer Selection in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Column-calculation-based-on-Slicer-Selection/m-p/4413779#M175292 <P>I have 3 Tables "Variables", "Sales Data" and "Scrap Ratio".</P><P>&nbsp;</P><P>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).</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Sold Month</TD><TD>Business Division</TD><TD>Sales Group</TD><TD>Model Group</TD><TD>Count</TD><TD>Age</TD><TD>Age Bucket</TD><TD>Remain Rate</TD><TD>Active Units</TD></TR><TR><TD>2009</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>803</TD><TD>99</TD><TD>&gt;10</TD><TD>0</TD><TD>0</TD></TR><TR><TD>2010</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>752</TD><TD>99</TD><TD>&gt;10</TD><TD>0</TD><TD>0</TD></TR><TR><TD>2011</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>735</TD><TD>99</TD><TD>&gt;10</TD><TD>0</TD><TD>0</TD></TR><TR><TD>2012</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>949</TD><TD>99</TD><TD>&gt;10</TD><TD>0</TD><TD>0</TD></TR><TR><TD>2013</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>1680</TD><TD>99</TD><TD>&gt;10</TD><TD>0</TD><TD>0</TD></TR><TR><TD>2014</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>1685</TD><TD>10</TD><TD>N-9</TD><TD>0.661</TD><TD>1114</TD></TR><TR><TD>2015</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>2795</TD><TD>9</TD><TD>N-8</TD><TD>0.683</TD><TD>1909</TD></TR><TR><TD>2016</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>2221</TD><TD>8</TD><TD>N-7</TD><TD>0.719</TD><TD>1597</TD></TR><TR><TD>2017</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>2085</TD><TD>7</TD><TD>N-6</TD><TD>0.76</TD><TD>1585</TD></TR><TR><TD>2018</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>1850</TD><TD>6</TD><TD>N-5</TD><TD>0.803</TD><TD>1486</TD></TR><TR><TD>2019</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>1059</TD><TD>5</TD><TD>N-4</TD><TD>0.852</TD><TD>902</TD></TR><TR><TD>2020</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>814</TD><TD>4</TD><TD>N-3</TD><TD>0.907</TD><TD>738</TD></TR><TR><TD>2021</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>823</TD><TD>3</TD><TD>N-2</TD><TD>0.96</TD><TD>790</TD></TR><TR><TD>2022</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>1768</TD><TD>2</TD><TD>N-1</TD><TD>0.985</TD><TD>1741</TD></TR><TR><TD>2023</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>1398</TD><TD>1</TD><TD>N</TD><TD>0.985</TD><TD>1377</TD></TR><TR><TD>2024</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>2429</TD><TD>0</TD><TD>CY</TD><TD>1</TD><TD>2429</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Excel Formulas :</P><P>Age:&nbsp;=IF(Variables!$B$1-'NV Data'!$A2&gt;10,99,Variables!$B$1-'NV Data'!A2)</P><P>Age Bucket =IFERROR(XLOOKUP(F2,'Scrap Ratio'!A:A,'Scrap Ratio'!B:B),"&gt;10")</P><P>Remaining Rate&nbsp;=IFERROR(XLOOKUP(F2,'Scrap Ratio'!A:A,'Scrap Ratio'!C:C),0)</P><P>&nbsp;</P><P>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.</P><P>&nbsp;</P><P>Scrap Ratio Table</P><TABLE><TBODY><TR><TD>Age No</TD><TD>Age N</TD><TD>Remain Rate</TD></TR><TR><TD>0</TD><TD>CY</TD><TD>1</TD></TR><TR><TD>1</TD><TD>N</TD><TD>0.985</TD></TR><TR><TD>2</TD><TD>N-1</TD><TD>0.985</TD></TR><TR><TD>3</TD><TD>N-2</TD><TD>0.96</TD></TR><TR><TD>4</TD><TD>N-3</TD><TD>0.907</TD></TR><TR><TD>5</TD><TD>N-4</TD><TD>0.852</TD></TR><TR><TD>6</TD><TD>N-5</TD><TD>0.803</TD></TR><TR><TD>7</TD><TD>N-6</TD><TD>0.76</TD></TR><TR><TD>8</TD><TD>N-7</TD><TD>0.719</TD></TR><TR><TD>9</TD><TD>N-8</TD><TD>0.683</TD></TR><TR><TD>10</TD><TD>N-9</TD><TD>0.661</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>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).</P><P>&nbsp;</P><P>In this case 2023 is selected in the Slicer, basis which "Age" column calculated and then the this pivot got refreshed.</P><P><img /></P><P>&nbsp;</P><P>I would apriciate your support in resolving this one.</P><P>&nbsp;</P><P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="148838" data-lia-user-login="amitchandak" class="lia-mention lia-mention-user">amitchandak</a>&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="19851" data-lia-user-login="Ashish_Mathur" class="lia-mention lia-mention-user">Ashish_Mathur</a>&nbsp;</P> Mon, 17 Feb 2025 12:06:28 GMT thameem10 2025-02-17T12:06:28Z Dynamic Column calculation based on Slicer Selection https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Column-calculation-based-on-Slicer-Selection/m-p/4413779#M175292 <P>I have 3 Tables "Variables", "Sales Data" and "Scrap Ratio".</P><P>&nbsp;</P><P>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).</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Sold Month</TD><TD>Business Division</TD><TD>Sales Group</TD><TD>Model Group</TD><TD>Count</TD><TD>Age</TD><TD>Age Bucket</TD><TD>Remain Rate</TD><TD>Active Units</TD></TR><TR><TD>2009</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>803</TD><TD>99</TD><TD>&gt;10</TD><TD>0</TD><TD>0</TD></TR><TR><TD>2010</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>752</TD><TD>99</TD><TD>&gt;10</TD><TD>0</TD><TD>0</TD></TR><TR><TD>2011</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>735</TD><TD>99</TD><TD>&gt;10</TD><TD>0</TD><TD>0</TD></TR><TR><TD>2012</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>949</TD><TD>99</TD><TD>&gt;10</TD><TD>0</TD><TD>0</TD></TR><TR><TD>2013</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>1680</TD><TD>99</TD><TD>&gt;10</TD><TD>0</TD><TD>0</TD></TR><TR><TD>2014</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>1685</TD><TD>10</TD><TD>N-9</TD><TD>0.661</TD><TD>1114</TD></TR><TR><TD>2015</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>2795</TD><TD>9</TD><TD>N-8</TD><TD>0.683</TD><TD>1909</TD></TR><TR><TD>2016</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>2221</TD><TD>8</TD><TD>N-7</TD><TD>0.719</TD><TD>1597</TD></TR><TR><TD>2017</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>2085</TD><TD>7</TD><TD>N-6</TD><TD>0.76</TD><TD>1585</TD></TR><TR><TD>2018</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>1850</TD><TD>6</TD><TD>N-5</TD><TD>0.803</TD><TD>1486</TD></TR><TR><TD>2019</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>1059</TD><TD>5</TD><TD>N-4</TD><TD>0.852</TD><TD>902</TD></TR><TR><TD>2020</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>814</TD><TD>4</TD><TD>N-3</TD><TD>0.907</TD><TD>738</TD></TR><TR><TD>2021</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>823</TD><TD>3</TD><TD>N-2</TD><TD>0.96</TD><TD>790</TD></TR><TR><TD>2022</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>1768</TD><TD>2</TD><TD>N-1</TD><TD>0.985</TD><TD>1741</TD></TR><TR><TD>2023</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>1398</TD><TD>1</TD><TD>N</TD><TD>0.985</TD><TD>1377</TD></TR><TR><TD>2024</TD><TD>Truck</TD><TD>Retail</TD><TD>MDL1</TD><TD>2429</TD><TD>0</TD><TD>CY</TD><TD>1</TD><TD>2429</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Excel Formulas :</P><P>Age:&nbsp;=IF(Variables!$B$1-'NV Data'!$A2&gt;10,99,Variables!$B$1-'NV Data'!A2)</P><P>Age Bucket =IFERROR(XLOOKUP(F2,'Scrap Ratio'!A:A,'Scrap Ratio'!B:B),"&gt;10")</P><P>Remaining Rate&nbsp;=IFERROR(XLOOKUP(F2,'Scrap Ratio'!A:A,'Scrap Ratio'!C:C),0)</P><P>&nbsp;</P><P>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.</P><P>&nbsp;</P><P>Scrap Ratio Table</P><TABLE><TBODY><TR><TD>Age No</TD><TD>Age N</TD><TD>Remain Rate</TD></TR><TR><TD>0</TD><TD>CY</TD><TD>1</TD></TR><TR><TD>1</TD><TD>N</TD><TD>0.985</TD></TR><TR><TD>2</TD><TD>N-1</TD><TD>0.985</TD></TR><TR><TD>3</TD><TD>N-2</TD><TD>0.96</TD></TR><TR><TD>4</TD><TD>N-3</TD><TD>0.907</TD></TR><TR><TD>5</TD><TD>N-4</TD><TD>0.852</TD></TR><TR><TD>6</TD><TD>N-5</TD><TD>0.803</TD></TR><TR><TD>7</TD><TD>N-6</TD><TD>0.76</TD></TR><TR><TD>8</TD><TD>N-7</TD><TD>0.719</TD></TR><TR><TD>9</TD><TD>N-8</TD><TD>0.683</TD></TR><TR><TD>10</TD><TD>N-9</TD><TD>0.661</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>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).</P><P>&nbsp;</P><P>In this case 2023 is selected in the Slicer, basis which "Age" column calculated and then the this pivot got refreshed.</P><P><img /></P><P>&nbsp;</P><P>I would apriciate your support in resolving this one.</P><P>&nbsp;</P><P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="148838" data-lia-user-login="amitchandak" class="lia-mention lia-mention-user">amitchandak</a>&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="19851" data-lia-user-login="Ashish_Mathur" class="lia-mention lia-mention-user">Ashish_Mathur</a>&nbsp;</P> Mon, 17 Feb 2025 12:06:28 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Column-calculation-based-on-Slicer-Selection/m-p/4413779#M175292 thameem10 2025-02-17T12:06:28Z Re: Dynamic Column calculation based on Slicer Selection https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Column-calculation-based-on-Slicer-Selection/m-p/4416585#M175377 <P>Hi,</P> <P>Not sure how much i can help but i would like to try.&nbsp; Share the download link of the MS Excel file with your formulas/Pivot Tables already there.&nbsp; It will be easier to understand your logic from there and convert it into measures.</P> Tue, 18 Feb 2025 23:06:03 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Column-calculation-based-on-Slicer-Selection/m-p/4416585#M175377 Ashish_Mathur 2025-02-18T23:06:03Z Re: Dynamic Column calculation based on Slicer Selection https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Column-calculation-based-on-Slicer-Selection/m-p/4418152#M175435 <P>Hi Ashish, Thanks for your reply. Please have this file link for your understanding of the formulaes.</P><P><A href="https://1drv.ms/x/c/8ae325be57c97015/EVRxlu-zPalGgIm2rf_72k4BLAaAVRqQRgQg5UyP7LDr2Q?e=JYowBt" target="_blank">Active Units by Selected Year.xlsx</A></P><P>&nbsp;</P><P>The Idea is to see the pivot data Active Units basis the selected year.</P> Wed, 19 Feb 2025 17:33:00 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Column-calculation-based-on-Slicer-Selection/m-p/4418152#M175435 thameem10 2025-02-19T17:33:00Z Re: Dynamic Column calculation based on Slicer Selection https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Column-calculation-based-on-Slicer-Selection/m-p/4418494#M175443 <P>Hi,</P> <P>PBI file attached.</P> <P><img /></P> <P>&nbsp;</P> Thu, 20 Feb 2025 00:29:33 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Column-calculation-based-on-Slicer-Selection/m-p/4418494#M175443 Ashish_Mathur 2025-02-20T00:29:33Z Re: Dynamic Column calculation based on Slicer Selection https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Column-calculation-based-on-Slicer-Selection/m-p/4597655#M176033 <P>Hi Ashish, Thanks a lot for solving this in Power BI. I apriciate your expertise in this space.</P> Thu, 06 Mar 2025 03:51:42 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Column-calculation-based-on-Slicer-Selection/m-p/4597655#M176033 thameem10 2025-03-06T03:51:42Z Re: Dynamic Column calculation based on Slicer Selection https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Column-calculation-based-on-Slicer-Selection/m-p/4597953#M176043 <P>You are welcome.</P> Thu, 06 Mar 2025 07:32:04 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Column-calculation-based-on-Slicer-Selection/m-p/4597953#M176043 Ashish_Mathur 2025-03-06T07:32:04Z