Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Percentage

Hi,

 

I want to work out the moving percentage of monthly balances vs total gross advance for the same year and create a line chart based on the result.

I was trying the below Dax calculation, but getting all zeros.

Percentage =
Var _Var1 = CALCULATE(sum(MainData[Sum_GrossAdvance]),MainData[Flag_Today] = 1 && MainData[Flag_Live] in {0,1} && MainData[Flag_Closed] in {0,1})
Var _Var2 = calculate(sum(MainData[Sum_Balance]))
Var _Var3 = divide([Total Balance],[Total Gross Advanced],0)
Return _Var3

 

Below is the screen print of the tables. 

1- is split of total balance per advance year and monthly snapshot date.

2 - gross advance

 

 

2 Replies

  • devanshi's avatar
    devanshi
    Icon for Helper V rankHelper V
    3 years ago

    Years = YEAR('Table'[Date])
    Total Gross Advance = SUM('Table'[Gross Advanced])
    Monthly Balance = SUM('Table'[Balance])
    Moving Percentage = DIVIDE( [Monthly Balance],
                                                          CALCULATE([Total Gross Advance], ALLEXCEPT('Table', 'Table'[Year],
                                                                                'Table'[Date] <= MAX('Table'[Date]]))))

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    It seems that both _Var1 and _Var2 are not used. Please try:

    Percentage =
VAR _Var1 = CALCULATE(SUM(MainData[Sum_GrossAdvance]), MainData[Flag_Today] = 1 && MainData[Flag_Live] IN {0, 1} && MainData[Flag_Closed] IN {0, 1})
VAR _Var2 = CALCULATE(SUM(MainData[Sum_Balance]))
VAR _Var3 = DIVIDE(_Var2, _Var1, 0)
RETURN _Var3

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum