Adding a sum to a table from list

[SvenJ]

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.

[SvenJ]

Great! Thanks for your help!

[SvenJ]

@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? 

 

[ValtteriN · Accepted solution]

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

[SvenJ]

@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)?

[ValtteriN]

@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. 

[SvenJ]

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

[ValtteriN]

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:

[ValtteriN]

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]

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

[ValtteriN]

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.