Forum Discussion

SvenJ's avatar
SvenJ
Icon for Helper III rankHelper 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's avatar
    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])

SvenJ's avatar
SvenJ
Icon for Helper III rankHelper 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's avatar
ValtteriN
Icon for Community Champion rankCommunity 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.