Percentage Calculation

[Fali324]

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%

[danextian · Accepted solution]

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.

[Fali324]

Hi, 

that doesn't work as all the sectors in a stacked graph show as 100%, any idea why that would happen?

[MichaelSamiotis]

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.

[MichaelSamiotis]

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.