topic Re: Percentage in DAX Commands and Tips

Percentage

Anonymous — Wed, 21 Jun 2023 14:30:00 GMT

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

Re: Percentage

devanshi — Thu, 22 Jun 2023 11:17:47 GMT

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]]))))

Re: Percentage

Anonymous — Fri, 23 Jun 2023 02:05:52 GMT

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