Forum Discussion

Helper III

4 years ago

Solved

Adding a sum to a table from list

Good morning, i have a list with values and a specific "position", which is a clear value at the table: Here is the table How can i get the list filled with the sum of the p...

ValtteriN
4 years ago
Hi,

Total calculation logic uses the same dax as within the columns/rows it is trying to calculate total. However in the total calculation filter context e.g. using SELECTEDVALUE() return blanks. To combat this you can change the calculation logic of the measure for total values e.g. IF(SELECTEDVALUE('Table'[month])=blank(),[total calculation logic],[original calculation logic])

ValtteriN

Community Champion

4 years ago

Hi,

You could Unpivot the table and then create either a columns or a measures for incoming and outgoing amounts. Additionally link the two tables with dimension tables. The condition for incoming and outgoing can be their value direction.

E.g. Incoming = IF(sum('Incomingfact'[Value])>=0,sum('Incomingfact'[Value]),BLANK())

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

SvenJ

Helper III

4 years ago

Thanks for your answer. Can you give me an example? I´m new on Power Bi and can´t follow you 100%

ValtteriN
Community Champion
4 years ago
Okay,

So for the steps:

Unpivot:
Select a column in powerquery and "unpivot other columns" or in this case select month column January, February....
You get a table like this:

Dimension tables:
So with this I mean tables that act as a slicer in between your two tables e.g. Calendar with Month column or a list of all the positions. Alternatively you can ignore this step and modify the measures for incoming and outgoing.

As an example here 'IncomingFact' table is a table that I unpivoted and 'Incoming' Table is the "list" table:

Incoming =
var _m =SELECTEDVALUE(Incoming[Month])
var _p = SELECTEDVALUE(Incoming[Position])
var _value = calculate(SUM(IncomingFact[Value]),all(IncomingFact),IncomingFact[Month]=_m,IncomingFact[Position]=_p)
return
IF(_value>0,_value,BLANK())

tables and end result using this dax:
'Incoming'

'IncomingFact'

End result:

For outgoing I reversed the "<" condition

This should be a more detailed explanation. Ping me with @ if you still have questions.