Sum values only with a threshold based on header level

[Penguin12]

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 Amount of the OrderItem when the sum of the header is bigger or equal then 1000. My idea was to first write a measure that always sums the amount for the header and then use it for filtering in another measure. But unfortunetly it did not work as intended. It basically filtered when the item was smaller then the threshold (1000) not the sum of the header level. 

How do I fix the first measure that it always, not depending on the context of the table, calculates the sum on the header level and checks if it is bigger then the threshold?

One example how it should be shown later

OrderItem	Amount
1	100
2	200
3	800
4	1005

[Penguin12]

@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 · Accepted solution]

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

[joaoribeiro]

Hi @Penguin12 ,

I think you use the following measure structure to solve your problem:

VAR _Table_Total_Header =
    ADDCOLUMNS (
        'Table',
        "Header_Total",
            CALCULATE (
                SUM ( 'Table'[Amount] ),
                ALLEXCEPT ( 'Table', 'Table'[OrderHeader] )
            )
    )
RETURN
    SUMX (
        FILTER ( '_Table_Total_Header', [Header_Total] >= 1000 ),
        'Table'[Amount]
    )

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