topic Re: Adding a sum to a table from list in DAX Commands and Tips

Adding a sum to a table from list

SvenJ — Sun, 16 Jan 2022 10:49:54 GMT

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 positions as shown in the example above. It would be great to get your help.

Re: Adding a sum to a table from list

ValtteriN — Sun, 16 Jan 2022 16:15:12 GMT

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!

Re: Adding a sum to a table from list

SvenJ — Sun, 16 Jan 2022 17:04:22 GMT

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

Re: Adding a sum to a table from list

ValtteriN — Sun, 16 Jan 2022 17:57:48 GMT

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.

Re: Adding a sum to a table from list

SvenJ — Sun, 16 Jan 2022 18:52:28 GMT

ValtteriN Thanks for the explanation. Where do i have to add the measure or function? Is the function correct? I don´t see the part where you grab the value from the INCOMING (list)?

Re: Adding a sum to a table from list

ValtteriN — Sun, 16 Jan 2022 20:10:33 GMT

SvenJ

I understood you want the values from the table with month names as columns. Did I misunderstand. In the DAX I categorize whether or not values are incoming or out going based on if they are positive or negative and then I created two measures based on this. E.g. if the value is -5 for position 4444 in January the result is outgoing = -5 and incoming = blank() for the month and position in question.

Re: Adding a sum to a table from list

SvenJ — Sun, 16 Jan 2022 20:50:23 GMT

ValtteriN No, the other way around. I want to read the values from the list and want the sum in the table with month

Re: Adding a sum to a table from list

ValtteriN — Sun, 16 Jan 2022 21:45:11 GMT

This clarifies things,

The unpivot step remains the same, but instead of 2 measures we can use one.

Example data with incoming + outgoing columns:

Dax:

Incoming/outgoing = var _m =SELECTEDVALUE(Incoming[Month])

var _p = SELECTEDVALUE(Incoming[Position])

var _value = calculate(SUM(IncomingFact[Incoming])-SUM(IncomingFact[OutGoing]),all(IncomingFact),IncomingFact[Month]=_m,IncomingFact[Position]=_p)

return

_value

End result:

Re: Adding a sum to a table from list

SvenJ — Mon, 17 Jan 2022 07:08:21 GMT

ValtteriN Thanks a lot - this helps! I think you switched the table names in one post, as i get the values from the list and not the table. Anyway, i found the problem. Another question. Is there a reason, why i don´t get the total amount in columns and rows?

Re: Adding a sum to a table from list

ValtteriN — Mon, 17 Jan 2022 07:53:46 GMT

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

Re: Adding a sum to a table from list

SvenJ — Mon, 17 Jan 2022 08:08:47 GMT

Great! Thanks for your help!