PBI Table Column Total Incorrect

[tsdick]

Hello Fellow Community Members, The following DAX expression is producing the correct row value, but the column total does not foot.  All other rows and columns in the matrix visual are correct.  Any suggestions?

Vol Qty Var = (

('Total Sales Grouped X'[Mix Vol PP]*'Total Sales Grouped X'[CP Sales Units])

-sumx('Total Sales Grouped X','Total Sales Grouped X'[Total units PP])

)

*sumx('Total Sales Grouped X','Total Sales Grouped X'[Price PP])

[tsdick · Accepted solution]

Hello JD, thanks for the suggestion -- I understand your point on a single use of SUMX.  Your DAX suggestion throws off the row totals in the Vol Qty Var fix column.  I am wondering if using a combination of columns (built using CALCULATE) and measures (built using SUMX) from the made table is tripping up the DAX calc's.  

My DAX... (green expressions are columns and purple expressions are measures)

Vol Qty Var = if(sumx('Total Sales Grouped X','Total Sales Grouped X'[Total units PP])=0,

'Total Sales Grouped X'[Qty Var $'s],

(('Total Sales Grouped X'[Mix Vol PP]*'Total Sales Grouped X'[CP Sales Units])

-sumx('Total Sales Grouped X',[Total units PP]))

*sumx('Total Sales Grouped X','Total Sales Grouped X'[Price PP]))

Your Suggested Fix...

Vol Qty Var fix = SUMX ( 'Total Sales Grouped X', ( 'Total Sales Grouped X'[Mix Vol PP] * 'Total Sales Grouped X'[CP Sales Units] ) - ( 'Total Sales Grouped X'[Total units PP] * 'Total Sales Grouped X'[Price PP] ) )

The circled results in the screen print are incorrect.

Thanks again for the assist.

TSDICK

[jdbuchanan71 · Accepted solution]

@tsdick 

Your measure in your original post did not have all of the lines from your measure in your second post.  Also, you should never include a table name when referencing a measure.  It makes it much harder to understand what is going on in your code.  So updating the measure from your second post we get this.

Vol Qty Var =
SUMX (
    'Total Sales Grouped X',
    IF (
        'Total Sales Grouped X'[Total units PP] = 0,
        [Qty Var $'s],
        [Mix Vol PP] * [CP Sales Units] - 'Total Sales Grouped X'[Total units PP] * 'Total Sales Grouped X'[Price PP]
    )
)

[jdbuchanan71]

@tsdick 

In your measures, if you use SUMX more than once, chances are you're doing something wrong.  SUMX is an iterator where you give it the table you want go through row by row then you give it the calculation you want it to do on every row.  For your calculation, you want to look at every row in the 'Total Sales Grouped X' table and do a calculation on it so that would be like this.

Vol Qty Var =
SUMX (
    'Total Sales Grouped X',
    ( 'Total Sales Grouped X'[Mix Vol PP] * 'Total Sales Grouped X'[CP Sales Units] ) - 
	( 'Total Sales Grouped X'[Total units PP] * 'Total Sales Grouped X'[Price PP] )
)

note* you don't need the ( ) around the first and second parts of your calculation, I just think it makes it easier to read.

Once it is done going through every row and doing the calculation it gives you the aggregation type you used.
SUMx, MINx, MAXx, AVERAGEx, etx.

[tsdick · Accepted solution]

Hello JD, thanks for the suggestion -- I understand your point on a single use of SUMX.  Your DAX suggestion throws off the row totals in the Vol Qty Var fix column.  I am wondering if using a combination of columns (built using CALCULATE) and measures (built using SUMX) from the made table is tripping up the DAX calc's.  

My DAX... (green expressions are columns and purple expressions are measures)

Vol Qty Var = if(sumx('Total Sales Grouped X','Total Sales Grouped X'[Total units PP])=0,

'Total Sales Grouped X'[Qty Var $'s],

(('Total Sales Grouped X'[Mix Vol PP]*'Total Sales Grouped X'[CP Sales Units])

-sumx('Total Sales Grouped X',[Total units PP]))

*sumx('Total Sales Grouped X','Total Sales Grouped X'[Price PP]))

Your Suggested Fix...

Vol Qty Var fix = SUMX ( 'Total Sales Grouped X', ( 'Total Sales Grouped X'[Mix Vol PP] * 'Total Sales Grouped X'[CP Sales Units] ) - ( 'Total Sales Grouped X'[Total units PP] * 'Total Sales Grouped X'[Price PP] ) )

The circled results in the screen print are incorrect.

Thanks again for the assist.

TSDICK