Totals adding up incorrectly in measure but not in column

[LewisB]

I have a calculated column that works fine, and ended up converting it into a measure.

This is the calculated column:

x-Updated Sales Switch = SWITCH(

    True(),

    '# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] >= DATE (2023,01,01) && '# Sales'[Invoice Date] <= DATE (2023,03,31), '# Sales'[Net Sales Amount]*1.4997,

    '# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] >= DATE (2022,10,01) && '# Sales'[Invoice Date] <= DATE (2022,12,31), '# Sales'[Net Sales Amount]*1.5302,

    '# Sales'[Net Sales Amount])

 
And this is the measure:

x-Updated Sales Measure = SUMX('# Sales',SWITCH(

    True(),

   SELECTEDVALUE('# Sales'[Company Code]) = "pus" && SELECTEDVALUE('# Sales'[Invoice Date])  >= DATE(2023,01,01) && SELECTEDVALUE('# Sales'[Invoice Date]) <= DATE(2023,03,31), SUMX('# Sales', '# Sales'[Net Sales Amount] * 1.4997),

    SELECTEDVALUE('# Sales'[Company Code]) = "pus" && SELECTEDVALUE('# Sales'[Invoice Date]) >= DATE(2022,10,01) && SELECTEDVALUE('# Sales'[Invoice Date]) <= DATE(2022,12,31), SUMX('# Sales', '# Sales'[Net Sales Amount] * 1.5302),

    SUM('# Sales'[Net Sales Amount])

))

The measure is pulling through the correct amounts, however the totals are not adding up correctly. Here is a screenshot of the column and measure side by side:

The measure seems to be multiplying the total by the number of lines in an invoice. This will have something to do with the SUMX function, as that calculates line by line, but I am unsure of how to solve this.

Are there any suggestions as to how I can go about fixing this? @amitchandak - you are familiar with my last issue, so you may be able to help here

[v-jianboli-msft · Accepted solution]

Hi @LewisB ,

Based on your description, I have created a simple sample:

Please try:

Measure =
VAR _a =
    ADDCOLUMNS (
        '# Sales',
        "C",
            SWITCH (
                TRUE (),
                '# Sales'[Company Code] = "pus"
                    && '# Sales'[Invoice Date] >= DATE ( 2023, 01, 01 )
                    && '# Sales'[Invoice Date] <= DATE ( 2023, 03, 31 ), '# Sales'[Net Sales Amount] * 1.4997,
                '# Sales'[Company Code] = "pus"
                    && '# Sales'[Invoice Date] >= DATE ( 2022, 10, 01 )
                    && '# Sales'[Invoice Date] <= DATE ( 2022, 12, 31 ), '# Sales'[Net Sales Amount] * 1.5302,
                '# Sales'[Net Sales Amount]
            )
    )
RETURN
    SUMX ( _a, [C] )

Final output:

Best Regards,

Jianbo Li

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

[LewisB]

Thanks @v-jianboli-msft  - this worked for me!