Calculate total variance in power bi with dax

Question

I have 3 measures:

1) total_trx = SUM(mytable[trx])
2) trx_prev_month = CALCULATE([total_trx], DATEADD(calendar[date], -1,MONTH))
3) monthly_var = DIVIDE([total_trx],[trx_prev_month])-1

If I add a waterfall viz, x-axis with month, it gives me the % of monthly variation and a TOTAL bar at the end that sums all the variations. I need to reproduce that total number in order to show a KPI as in "so far, we've increased ...%", changing when using a date slicer.

Seems like sum(monthly_var) is not allowed.

Any ideas?

Thank you very much.

Edit1: sample with date filter = Last 4 months

Jul 100   0%
Aug 110  10%
Sep  90 -20%
Oct  80 -10%

Total: -20% <- need a dax to calculate this number and show just -20%

Then if I change the filter to, for example LAST 6 MONTHS, I need to calculate it up to May

Can you add some sample data and your expected output from them? — mkRabbani
Thanks mkRabbani, I tried to put an example of what I need to calculate. Hope that helps . — gusch

BOUBRIT Nacim BOUBRIT Nacim · Accepted Answer · 2020-11-13T20:22:34

In order to get the desired result we will use an intermediate table in our query that will summarize the results by months:

use this code and replace calendar[Year Month] with your Year month column :

SUMX(
      SUMMARIZECOLUMNS(calendar[Year Month],"Monthly_int_var",[monthly_var]),
      [Monthly_int_var]
    )

Calculate total variance in power bi with dax

1 Answers