Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
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])))
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
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])))
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.
Process Date | Month End Date | Collateral Age Category | Current Balance | Charge Off Balance | Original Yield | Current Yield | Charge Off Yield |
20211031 | 10/31/2021 | 0-3 Years | 7803.00 | 0.00 | 628.20 | 272.32 | 0.00 |
20211031 | 10/31/2021 | 10+ Years | 2896.71 | 0.00 | 223.42 | 79.37 | 0.00 |
20211031 | 10/31/2021 | 10+ Years | 3985.06 | 0.00 | 1086.02 | 426.40 | 0.00 |
20211031 | 10/31/2021 | 4-6 Years | 9771.99 | 0.00 | 701.26 | 366.45 | 0.00 |
20211031 | 10/31/2021 | 4-6 Years | 14219.35 | 0.00 | 620.12 | 425.16 | 0.00 |
20211031 | 10/31/2021 | 7-9 Years | 2949.00 | 0.00 | 488.72 | 88.18 | 0.00 |
20211031 | 10/31/2021 | 7-9 Years | 10590.52 | 0.00 | 754.21 | 443.74 | 0.00 |
20211231 | 12/31/2021 | 0-3 Years | 15468.36 | 0.00 | 358.20 | 307.82 | 0.00 |
20211231 | 12/31/2021 | 0-3 Years | 7043.66 | 0.00 | 628.20 | 245.82 | 0.00 |
20211231 | 12/31/2021 | 10+ Years | 4260.11 | 0.00 | 205.24 | 148.68 | 0.00 |
20211231 | 12/31/2021 | 10+ Years | 2347.79 | 0.00 | 481.41 | 81.94 | 0.00 |
20211231 | 12/31/2021 | 4-6 Years | 35313.21 | 0.00 | 1962.98 | 1500.81 | 0.00 |
20211231 | 12/31/2021 | 4-6 Years | 19607.49 | 0.00 | 537.50 | 421.56 | 0.00 |
20211231 | 12/31/2021 | 7-9 Years | 12750.21 | 0.00 | 974.10 | 535.51 | 0.00 |
20211231 | 12/31/2021 | 7-9 Years | 0.00 | 0.00 | 496.64 | 0.00 | 0.00 |
20220228 | 2/28/2022 | 0-3 Years | 14739.73 | 0.00 | 358.20 | 293.32 | 0.00 |
20220228 | 2/28/2022 | 0-3 Years | 6279.65 | 0.00 | 628.20 | 219.16 | 0.00 |
20220228 | 2/28/2022 | 10+ Years | 715.54 | 0.00 | 481.41 | 24.97 | 0.00 |
20220228 | 2/28/2022 | 10+ Years | 427.32 | 0.00 | 232.47 | 8.50 | 0.00 |
20220228 | 2/28/2022 | 4-6 Years | 8725.04 | 0.00 | 701.26 | 327.19 | 0.00 |
20220228 | 2/28/2022 | 4-6 Years | 12628.76 | 0.00 | 620.12 | 377.60 | 0.00 |
20220228 | 2/28/2022 | 7-9 Years | 10988.47 | 0.00 | 2635.97 | 1895.51 | 0.00 |
20220228 | 2/28/2022 | 7-9 Years | 1830.75 | 0.00 | 488.72 | 54.74 | 0.00 |
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |