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.

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

Adding a sum to a table from list | Microsoft Fabric Community

10 Replies

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.
SvenJ
Helper III
4 years ago
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
  Community Champion
  4 years ago
  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
    Helper III
    4 years ago
    ValtteriN No, the other way around. I want to read the values from the list and want the sum in the table with month
SvenJ
Helper III
4 years ago
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
  Community Champion
  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
Helper III
4 years ago
Great! Thanks for your help!

Forum Discussion

Adding a sum to a table from list

10 Replies

Related content

Adding columns

adding variance into table

Adding comments to specific row in table visual

Adding calculated column to date table

Adding Hyperlinks to a Table