Forum Discussion
Percentage Calculation
Hi
I have two tables,
Fee Date -
Date
Sector
New Fee
Financial Year
Date
FY
FQ
I've linked both the table using the date column and want to create a graph which shows the percentage contribution for each sector. I have a hierachy of FY & FQ so if I'm at the highest level it will show overall distibution for the whole FY but if I drill down to FQ it should show the contribution based on the quaters.
How can I achieive this?
Here is my sample data and Percentage Contribution is what I want to achieve, I've based it on quaters for an example
| FY | FQ | Date - Copy | Sector | New Fee | Year Total | Percentage Contribution |
| 2024 | Q1 | 01/04/2024 00:00 | Sector 1 | £821,000.00 | £ 27,270,000 | 3% |
| 2024 | Q1 | 01/04/2024 00:00 | Sector 2 | £5,372,000.00 | £ 27,270,000 | 20% |
| 2024 | Q1 | 01/04/2024 00:00 | Sector 3 | £269,000.00 | £ 27,270,000 | 1% |
| 2024 | Q2 | 01/07/2024 00:00 | Sector 1 | £804,000.00 | £ 27,270,000 | 3% |
| 2024 | Q2 | 01/07/2024 00:00 | Sector 2 | £5,336,000.00 | £ 27,270,000 | 20% |
| 2024 | Q2 | 01/07/2024 00:00 | Sector 3 | £244,000.00 | £ 27,270,000 | 1% |
| 2024 | Q3 | 01/10/2024 00:00 | Sector 1 | £788,000.00 | £ 27,270,000 | 3% |
| 2024 | Q3 | 01/10/2024 00:00 | Sector 2 | £5,802,000.00 | £ 27,270,000 | 21% |
| 2024 | Q3 | 01/10/2024 00:00 | Sector 3 | £340,000.00 | £ 27,270,000 | 1% |
| 2024 | Q4 | 01/01/2025 00:00 | Sector 1 | £795,000.00 | £ 27,270,000 | 3% |
| 2024 | Q4 | 01/01/2025 00:00 | Sector 2 | £5,484,000.00 | £ 27,270,000 | 20% |
| 2024 | Q4 | 01/01/2025 00:00 | Sector 3 | £601,000.00 | £ 27,270,000 | 2% |
| 2024 | Q4 | 01/01/2025 00:00 | Sector 4 | £614,000.00 | £ 27,270,000 | 2% |
Hi Fali324
Assuming that for FY level, the contribution is based on the year total over the total of all years and sectors, try this:
Contribution% = VAR _FQ = DIVIDE ( SUM ( 'DataTable'[New Fee] ), CALCULATE ( SUM ( 'DataTable'[New Fee] ), ALL ( FYFQ[FQ] ), ALL ( 'DataTable'[Sector] ) ) ) VAR _FY = DIVIDE ( SUM ( 'DataTable'[New Fee] ), CALCULATE ( SUM ( 'DataTable'[New Fee] ), ALL ( FYFQ[FY] ), ALL ( 'DataTable'[Sector] ) ) ) RETURN IF ( ISINSCOPE ( FYFQ[FQ] ), _FQ, _FY )Otherwise, please provide the expected result for the year level and the reasoning behind.
Please see the attached pbix.
4 Replies
Hi Fali324
Assuming that for FY level, the contribution is based on the year total over the total of all years and sectors, try this:
Contribution% = VAR _FQ = DIVIDE ( SUM ( 'DataTable'[New Fee] ), CALCULATE ( SUM ( 'DataTable'[New Fee] ), ALL ( FYFQ[FQ] ), ALL ( 'DataTable'[Sector] ) ) ) VAR _FY = DIVIDE ( SUM ( 'DataTable'[New Fee] ), CALCULATE ( SUM ( 'DataTable'[New Fee] ), ALL ( FYFQ[FY] ), ALL ( 'DataTable'[Sector] ) ) ) RETURN IF ( ISINSCOPE ( FYFQ[FQ] ), _FQ, _FY )Otherwise, please provide the expected result for the year level and the reasoning behind.
Please see the attached pbix.
Hi Fali324 ,
I would calculate 3 measures to do that.TotalNewFee = SUM('Fee Date'[New Fee])TotalFeeByPeriod = CALCULATE( SUM('Fee Date'[New Fee]), ALLSELECTED('Fee Date'[Sector]) // Keeps context for other filters while ignoring Sector )PercentageContribution = DIVIDE( [TotalNewFee], [TotalFeeByPeriod], 0 )✅ Please let me know if that works. If so please mark this as a solution so otheres can benefit too.
Hi,
that doesn't work as all the sectors in a stacked graph show as 100%, any idea why that would happen?
The issue might be because of the Stacked Column Chart.
- Insert a Stacked Column Chart.
- Add the following fields:
- X-Axis: FY => FQ (drag both to create a hierarchy for drill-down).
- Legend: Sector (the breakdown by sector).
- Values: PercentageContribution (this measure makes sure that the correct percentage is calculated).
Please let me know if that works. If so please mark this as a solution so otheres can benefit too.
✅Please let me know if that works. If so please mark this as a solution so otheres can benefit too.
