topic Re: Power BI DAX to split the value in to multiple ranges in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Power-BI-DAX-to-split-the-value-in-to-multiple-ranges/m-p/709566#M1021 <P>Anonymous</LI-USER> does this looks correct?<img /></P> Fri, 07 Jun 2019 15:26:29 GMT parry2k 2019-06-07T15:26:29Z Power BI DAX to split the value in to multiple ranges https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Power-BI-DAX-to-split-the-value-in-to-multiple-ranges/m-p/708959#M989 <DIV class="votecell post-layout--left"><DIV class="js-voting-container grid fd-column ai-stretch gs4 fc-black-200"><DIV class="js-favorite-count mt8"><SPAN>I have the data as below.</SPAN></DIV><DIV class="js-favorite-count mt8">&nbsp;</DIV></DIV></DIV><DIV class="postcell post-layout--right"><DIV class="post-text"><TABLE><TBODY><TR><TD>Product_Code</TD><TD>Channel&nbsp;</TD><TD>Account_Id</TD><TD>Max_tier</TD><TD>Balance</TD></TR><TR><TD>2000-0100</TD><TD>Direct</TD><TD>102123625</TD><TD>5000</TD><TD>174589.26</TD></TR><TR><TD>2000-0100</TD><TD>Direct</TD><TD>102123625</TD><TD>10000</TD><TD>174589.26</TD></TR><TR><TD>2000-0100</TD><TD>Direct</TD><TD>102123625</TD><TD>20000</TD><TD>174589.26</TD></TR><TR><TD>2000-0100</TD><TD>Direct</TD><TD>102123625</TD><TD>50000</TD><TD>174589.26</TD></TR><TR><TD>2000-0100</TD><TD>Direct</TD><TD>102123625</TD><TD>99999999999.99</TD><TD>174589.26</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I am trying to build a report in Power BI, where the Balance should be split into a range that should fit into MAX_TIER.So the output should be as below</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Product_Code</TD><TD>Channel_Desc</TD><TD>Account_Id</TD><TD>Max_tier</TD><TD>Balance</TD><TD>Split_range</TD></TR><TR><TD>2000-0100</TD><TD>Direct</TD><TD>102123625</TD><TD>5000</TD><TD>174589.26</TD><TD>5000</TD></TR><TR><TD>2000-0100</TD><TD>Direct</TD><TD>102123625</TD><TD>10000</TD><TD>0</TD><TD>10000</TD></TR><TR><TD>2000-0100</TD><TD>Direct</TD><TD>102123625</TD><TD>20000</TD><TD>0</TD><TD>20000</TD></TR><TR><TD>2000-0100</TD><TD>Direct</TD><TD>102123625</TD><TD>50000</TD><TD>0</TD><TD>50000</TD></TR><TR><TD>2000-0100</TD><TD>Direct</TD><TD>102123625</TD><TD>99999999999.99</TD><TD>0</TD><TD>89589.26</TD></TR></TBODY></TABLE><P>Any thought would be much appreciated</P></DIV></DIV> Fri, 07 Jun 2019 00:39:17 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Power-BI-DAX-to-split-the-value-in-to-multiple-ranges/m-p/708959#M989 Anonymous 2019-06-07T00:39:17Z Re: Power BI DAX to split the value in to multiple ranges https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Power-BI-DAX-to-split-the-value-in-to-multiple-ranges/m-p/708965#M990 <P>Anonymous</LI-USER> what is the logic to get balance to last row?</P> Fri, 07 Jun 2019 01:11:06 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Power-BI-DAX-to-split-the-value-in-to-multiple-ranges/m-p/708965#M990 parry2k 2019-06-07T01:11:06Z Re: Power BI DAX to split the value in to multiple ranges https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Power-BI-DAX-to-split-the-value-in-to-multiple-ranges/m-p/708978#M995 <P>Hi Parry,</P><P>The balance is&nbsp;174589.26.</P><P>So we split into the MAX_tier range,</P><P>then the 1st range is 5000. So the remaining balance is 174589.26-5000 = 169589.26.</P><P>2nd split max is 10000. So the remaining balance is&nbsp; 169589.26 - 10000 = 159589.26</P><P>3rd split max is 20000. So the remaining balance is&nbsp; 159589.26 - 20000 = 139589.26</P><P>4th split max is 50000. So the remaining balance is&nbsp; 139589.26 - 50000 = 89589.26</P><P>5th split max is 9999999999.99. So the remaining balance is&nbsp; 89589.26</P><P>&nbsp;</P><P>Another Example</P><P>if the balance is only 3500. Then this will fit into the 1st range itself</P><P>if the balance is 8500. Then this will fit into the 1st and 2nd range&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P> Fri, 07 Jun 2019 01:45:08 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Power-BI-DAX-to-split-the-value-in-to-multiple-ranges/m-p/708978#M995 Anonymous 2019-06-07T01:45:08Z Re: Power BI DAX to split the value in to multiple ranges https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Power-BI-DAX-to-split-the-value-in-to-multiple-ranges/m-p/709566#M1021 <P>Anonymous</LI-USER> does this looks correct?<img /></P> Fri, 07 Jun 2019 15:26:29 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Power-BI-DAX-to-split-the-value-in-to-multiple-ranges/m-p/709566#M1021 parry2k 2019-06-07T15:26:29Z Re: Power BI DAX to split the value in to multiple ranges https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Power-BI-DAX-to-split-the-value-in-to-multiple-ranges/m-p/710119#M1038 <P>Hi Parry,</P><P>That is exactly what i am looking for.</P> Sun, 09 Jun 2019 22:19:42 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Power-BI-DAX-to-split-the-value-in-to-multiple-ranges/m-p/710119#M1038 Anonymous 2019-06-09T22:19:42Z