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.

danextian's avatar
danextian
Super User
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.