Calculate Column Sum of a Generated Table - DAX

Question

I've been trying to get the sum of the following table's column VALUE using DaxStudio in order to put that on PowerBI once is's ok, since PBI can get slow if you test a code for large calculated tables.

Table from DaxStudio

   BU              VALUE    
   ------------------------
   FOODS            0.0000
   FIBI             0.0000
   GEOS/CIS         0.7300
   CASC 
   S_S  
   SGS  
   COCOA    
   COCOA/SSSA   
   CORPORATE    
   N/A  
   CIS

The code behind it:

DEFINE
    VAR TOTAL =
        CALCULATE (
            SUMX (
                PACKAGING_POWERBI_YEARLY_2;
                PACKAGING_POWERBI_YEARLY_2[QUANTIDADE_ANTERIOR]
            );
            FILTER (
                PACKAGING_POWERBI_YEARLY_2;
                PACKAGING_POWERBI_YEARLY_2[DATE] = "2018"
                    && PACKAGING_POWERBI_YEARLY_2[CODIGO] = "43130"
            )
        )
EVALUATE
SUMMARIZE (
    CALCULATETABLE (
        FILTER (
            PACKAGING_POWERBI_YEARLY_2;
            PACKAGING_POWERBI_YEARLY_2[PRECO_PONDERADO] <> BLANK ()
                && PACKAGING_POWERBI_YEARLY_2[PRECO_PONDERADO] <> 0
        )
    );
    PACKAGING_POWERBI_YEARLY_2[BU];
    "VALUE"; FORMAT (
        (
            CALCULATE (
                SUMX (
                    PACKAGING_POWERBI_YEARLY_2;
                    PACKAGING_POWERBI_YEARLY_2[QUANTIDADE_ANTERIOR]
                );
                FILTER (
                    PACKAGING_POWERBI_YEARLY_2;
                    PACKAGING_POWERBI_YEARLY_2[DATE] = "2018"
                        && PACKAGING_POWERBI_YEARLY_2[CODIGO] = "43130"
                        && PACKAGING_POWERBI_YEARLY_2[PRECO_PONDERADO] <> BLANK ()
                )
            ) / TOTAL
        )
            * (
                CALCULATE (
                    SUMX ( PACKAGING_POWERBI_YEARLY_2; PACKAGING_POWERBI_YEARLY_2[PRECO_PONDERADO] );
                    FILTER (
                        PACKAGING_POWERBI_YEARLY_2;
                        PACKAGING_POWERBI_YEARLY_2[DATE] = "2018"
                            && PACKAGING_POWERBI_YEARLY_2[CODIGO] = "43130"
                            && PACKAGING_POWERBI_YEARLY_2[PRECO_PONDERADO] <> BLANK ()
                    )
                )
            );
        "0.0000"
    )
)

Instead of generating the table I would like to sum the results of column VALUE, but the only way I got to plot a result without error is through a "CALCULATED TABLE" (above).

Every filter I try leads to an error.

The idea is to get just the sum of that calculated column VALUE:

   0.7300

But every simpler filter or SUMX condition I try, an error pops-up.

Alexis Olson Alexis Olson · Accepted Answer · 2019-06-26T18:59:10

I'd think you could clean it up to look something like this:

CALCULATE (
    SUMX (
        VALUES ( PACKAGING_POWERBI_YEARLY_2[BU] ),
        SUM ( PACKAGING_POWERBI_YEARLY_2[QUANTIDADE_ANTERIOR] )
            / CALCULATE (
                SUM ( PACKAGING_POWERBI_YEARLY_2[QUANTIDADE_ANTERIOR] ),
                ALL ( PACKAGING_POWERBI_YEARLY_2[BU] )
            )
            * SUM ( PACKAGING_POWERBI_YEARLY_2[PRECO_PONDERADO] )
    ),
    FILTER (
        PACKAGING_POWERBI_YEARLY_2,
        PACKAGING_POWERBI_YEARLY_2[DATE] = "2018"
            && PACKAGING_POWERBI_YEARLY_2[CODIGO] = "43130"
            && PACKAGING_POWERBI_YEARLY_2[PRECO_PONDERADO] <> BLANK ()
            && PACKAGING_POWERBI_YEARLY_2[PRECO_PONDERADO] <> 0
    )
)

This uses SUMX to iterate through each of the BU values and for each one, calculates the value

(QUANTIDADE_ANTERIOR / Total QUANTIDADE_ANTERIOR ) * PRECO_PONDERADO

where the filters are reused instead of repeated.

I can't guarantee that this code works exactly as intended, but it should point you in a better direction.

Calculate Column Sum of a Generated Table - DAX

Table from DaxStudio

The code behind it:

1 Answers