Forum Discussion
Do math with matrix cells
Hello!
I'm pretty new to PowerBI and still learning the ropes. But to not ramble too much, here's my question - I have a table that I'm trying to add a new value to by using other values within the table.
The goal is to add a row which calculates the exiting MMs by taking the previous month's total, plus the number of entering MMs, less the current month's total (i.e. algebra solving for "exiting" in the equation, current MMs = prior MMs + entering - exiting).
For example, in 201802 I need to take the 201801 Total_H + 201802 Entering_MMs - 201802 Total_H.
My data drop for this summary is not of sufficient granularity to calculate my desired result directly, therefore I must calculate it using other measures. Thanks in advance for your time and assistance!
You may modify the measure like pattern below:
Result = VAR Current_Month = MAX ( Table[Month] ) VAR Previous_MMs = CALCULATE ( MAX ( Table[MMs] ), FILTER ( Table, Table[Month] = Current_Month - 1 ) ) VAR Current_Entering_MMs = MAX ( Table[Entering MMs] ) VAR Current_Total_H = MAX ( Table[Total H] ) RETURN Previous_MMs + Current_Entering_MMs - Current_Total_HCommunity Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
You may modify the measure like pattern below:
Result = VAR Current_Month = MAX ( Table[Month] ) VAR Previous_MMs = CALCULATE ( MAX ( Table[MMs] ), FILTER ( Table, Table[Month] = Current_Month - 1 ) ) VAR Current_Entering_MMs = MAX ( Table[Entering MMs] ) VAR Current_Total_H = MAX ( Table[Total H] ) RETURN Previous_MMs + Current_Entering_MMs - Current_Total_HCommunity Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks! This got me 95% of the way there and it was easy to figure out the rest.
Can you share sample data and sample output.
