Forum Discussion

Fali324's avatar
Fali324
Helper II
1 year ago
Solved

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 percentag...
Need Help
Tips and Tricks
  • danextian's avatar
    danextian
    1 year ago

    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.

MichaelSamiotis's avatar
MichaelSamiotis
Resolver I
1 year ago

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.