DAX SUMMARIZE not summarizing on the group-by level as expected

[clagro]

Hi all,

I am working on a measure where the sum should show 0 if negative based on product. The calculation should be done on row level before summarizing the result (I do not want to Summaize on aggrigated level).

When I do the following in a test measure where everything is in the same table, without filters and the measure in the MAX function is based on values of that table it works fine:

Measure =

SUMX (

SUMMARIZE (

'Table',

'Table'[Product], 

"Column1",          

MAX ([SUMA] + [SUMB] , 0)

)

, [Column1] )

The result comes out great:

Product | SUMA | SUMB | Measure

A           | 20        | -30     | 0

B           | 10        | -5       | 5

Total      | 30        | -35    | 5

However with my real example the measures are based on filtered values from a fact table and I want the grouping to be on values from a dim table the result is not working.

Code is as follow:

VAR inventoryQuantity =

    CALCULATE(

        [Quantity],

        FILTER('Agreement Type', [Agreement Type]  = "Inventory"),

)

VAR salesQuantity =

    CALCULATE(

        [Quantity],

        FILTER(''Agreement Type', [Agreement Type]  = "Sales")

)

    SUMX (

        SUMMARIZE (

            'Position',

            'Product Group'[Product Group Name],  

            "UIColumn",          

            MAX (inventoryQuantity + salesQuantity , 0)

            ),

        [UIColumn]

    )

The result lookes like this:

Product group | inventoryQuantity | salesQuantity | Result

A                      | 200                        | -500               | 0

B                       | 100                        | -50                 | 50

Total                 | 300                        | -550                | 0

The Total for Result should show 50 and not 0.

Can you please help me understand how to fix this?

Thanks!

[Anonymous]

Hi @clagro ,

Measure are counted by rows on a table. You can use a measure that uses an IF statement and counts the rows to find out if it is a total row (which means it will not be counted). Alternatively, you can perform the same operation using the HASONEFILTER formula.

See the link for more details:

Why my measure returns the wrong total? How to fix that? (vahiddm.com)

How to Make Measures Total Correctly in Power BI Tables - ArcherPoint

If these didn't solve your problem. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[clagro]

Update:

I got it working by moving inventoryQuantoty and salesQuantity to measures instead of having it in the code as variables. 

This makes no sence to me, can someone help me understand why please?