Forum Discussion

Penguin12's avatar
Penguin12
New Member
2 years ago
Solved

Sum values only with a threshold based on header level

Hi! I have this kind of data structure    OrderHeader OrderItem Amount A 1 100 A 2 200 B 5 200 A 3 800 C 4 1005   Now I need a DAX measure that only sums the Amo...
Need Help
  • joaoribeiro's avatar
    joaoribeiro
    2 years ago

    Hi Penguin12 ,

     

    In this case, to reuse this logic in multiple measures in a performatic way, my suggestion is to create a calculated column in the original table to have the total for each header. For this you can use the same logic in the previous reply: 

    NewColumn = 
CALCULATE (
    SUM ( 'Table'[Amount] ),
    ALLEXCEPT ( 'Table', 'Table'[OrderHeader] )
)​


    Then you can use this column to filter as needed using CALCULATE/FILTER.

    Otherwise, just use the variable below in each measure to calculate the table on the fly (the is no problem with this approach).

    VAR _Table_Total_Header =
    ADDCOLUMNS (
        'Table',
        "Header_Total",
            CALCULATE (
                SUM ( 'Table'[Amount] ),
                ALLEXCEPT ( 'Table', 'Table'[OrderHeader] )
            )
    )

     

     

    Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍

    Thanks!

    Best regards,
    Joao Ribeiro

     

Penguin12's avatar
Penguin12
New Member
2 years ago

joaoribeiro thank you very much for reply! But is there a way to rather have it in two measures so I can use the second one for example in different contexts in tables instead of creating a new table? I have been struggeling with this and was not able to rewrite it

joaoribeiro's avatar
joaoribeiro
Impactful Individual
2 years ago

Hi Penguin12 ,

 

In this case, to reuse this logic in multiple measures in a performatic way, my suggestion is to create a calculated column in the original table to have the total for each header. For this you can use the same logic in the previous reply: 

NewColumn = 
CALCULATE (
    SUM ( 'Table'[Amount] ),
    ALLEXCEPT ( 'Table', 'Table'[OrderHeader] )
)​


Then you can use this column to filter as needed using CALCULATE/FILTER.

Otherwise, just use the variable below in each measure to calculate the table on the fly (the is no problem with this approach).

VAR _Table_Total_Header =
    ADDCOLUMNS (
        'Table',
        "Header_Total",
            CALCULATE (
                SUM ( 'Table'[Amount] ),
                ALLEXCEPT ( 'Table', 'Table'[OrderHeader] )
            )
    )

 

 

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro