Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jamoroso
Helper I
Helper I

Percentages of Subtotal

Hi,

I am new to Power BI and need help with a measure(s) calculation. I have a Month End Date slicer and you can select from 1 to 13 Month End dates. I would like the percentage calculated to be based on per ProcessDate (Month End Date) and not on the Grand Total. If I select 02/28/2022 and 01/31/2022, I want the total percentage for 02/28/2022 to be 100% and for 01/31/2022 to be 100% and not use the combined total to calc the percentage. I know I have selected the 'Percent of grand total' on my value because it was the only way I could get a single date to work. The formula for the % is:

 

Percentage of Net Yield = ((SUM(Sheet1[Current Yield]) -sum(Sheet1[Charge Off Balance]))/SUM(Sheet1[Current Balance]))*SUM(Sheet1[Current Balance])
 
I will post screen shots and table in a follow up.
If you need more info, please let me know. 
 

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @jamoroso 

 

You can use the following measures

Net Yield = SUM('Table'[Current Yield]) - SUM('Table'[Charge Off Balance])
Percentage of Net Yield = DIVIDE([Net Yield],CALCULATE([Net Yield],ALL('Table'[Collateral Age Category])))

vjingzhang_0-1647506958748.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

4 REPLIES 4
v-jingzhang
Community Support
Community Support

Hi @jamoroso 

 

You can use the following measures

Net Yield = SUM('Table'[Current Yield]) - SUM('Table'[Charge Off Balance])
Percentage of Net Yield = DIVIDE([Net Yield],CALCULATE([Net Yield],ALL('Table'[Collateral Age Category])))

vjingzhang_0-1647506958748.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Thank you. This does work with the data I gave you, but for some reason I still cannot get it to calculate right in my report. I provided a very small sample (column and row wise) and see that it works with Excel as the data source. My report is using SQL Server tables and will still not calculate correctly. I at least have the format now for the calculation and will keep trying in my report. Thanks again. 

-Janet

 

03/28/2022 - I discovered the problem was on the Data Source Import. I am using a stored procedure and had to check off 'Navigate using full hierarchy' on the import. The above formula's then worked fine. 

jamoroso
Helper I
Helper I

Process DateMonth End DateCollateral Age CategoryCurrent BalanceCharge Off BalanceOriginal YieldCurrent YieldCharge Off Yield
2021103110/31/20210-3 Years7803.000.00628.20272.320.00
2021103110/31/202110+ Years2896.710.00223.4279.370.00
2021103110/31/202110+ Years3985.060.001086.02426.400.00
2021103110/31/20214-6 Years9771.990.00701.26366.450.00
2021103110/31/20214-6 Years14219.350.00620.12425.160.00
2021103110/31/20217-9 Years2949.000.00488.7288.180.00
2021103110/31/20217-9 Years10590.520.00754.21443.740.00
2021123112/31/20210-3 Years15468.360.00358.20307.820.00
2021123112/31/20210-3 Years7043.660.00628.20245.820.00
2021123112/31/202110+ Years4260.110.00205.24148.680.00
2021123112/31/202110+ Years2347.790.00481.4181.940.00
2021123112/31/20214-6 Years35313.210.001962.981500.810.00
2021123112/31/20214-6 Years19607.490.00537.50421.560.00
2021123112/31/20217-9 Years12750.210.00974.10535.510.00
2021123112/31/20217-9 Years0.000.00496.640.000.00
202202282/28/20220-3 Years14739.730.00358.20293.320.00
202202282/28/20220-3 Years6279.650.00628.20219.160.00
202202282/28/202210+ Years715.540.00481.4124.970.00
202202282/28/202210+ Years427.320.00232.478.500.00
202202282/28/20224-6 Years8725.040.00701.26327.190.00
202202282/28/20224-6 Years12628.760.00620.12377.600.00
202202282/28/20227-9 Years10988.470.002635.971895.510.00
202202282/28/20227-9 Years1830.750.00488.7254.740.00
jamoroso
Helper I
Helper I

Single Date Selection.JPGMultiple Date Selection.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.