PBI Matrix Column Totals (w/ multiple grouping row set up)

[tsdick]

Hello Fellow Community Members, the following PBI table (w/ 4 groupings - make, model, ledger..., acct...) is producing the expected results except for the Total value below the 2 right most columns (calculated like a row, but should be a column total).  I have found and tried many solutions for a single grouping but none seem to work for the multiple grouping output needed.  I am seeking assistance resolving the Total row price and quantity variance totals necessary for the waterfall chart visual.  Thanks for any insight assistance you can provide.  Tim

[jdbuchanan71 · Accepted solution]

One problem is, you are using SUMX too much, you only need to create the iteration once, like this.

Qty Var $'s =
SUMX (
    'Total Sales Grouped X',
    IF (
        'Total Sales Grouped X'[total units pp] = 0,
        'Total Sales Grouped X'[Price cp],
        'Total Sales Grouped X'[Price pP]
    ) * 'Total Sales Grouped X'[Quantity Var]
)

[jdbuchanan71 · Accepted solution]

One problem is, you are using SUMX too much, you only need to create the iteration once, like this.

Qty Var $'s =
SUMX (
    'Total Sales Grouped X',
    IF (
        'Total Sales Grouped X'[total units pp] = 0,
        'Total Sales Grouped X'[Price cp],
        'Total Sales Grouped X'[Price pP]
    ) * 'Total Sales Grouped X'[Quantity Var]
)

[jdbuchanan71]

@tsdick Please copy the text of the measure into your post rather than just a screenshot of the formula bar.   You should also include the screenshot so we have context but retyping a formula that long is error-prone.

[jdbuchanan71]

@tsdick 

Try it like this.

Price Var $'s = 
SUMX ( 'Total Sales Grouped X', [Price Var] * [total units cp] )

Although it looks like [total units cp] is a column in a table and not a measure, is that correct?  If that's the case you always want to include the table name with the column reference like this.

Price Var $'s = 
SUMX ( 'Total Sales Grouped X', [Price Var] * 'Total Sales Grouped X'[total units cp])

Assuming that column is on the 'Total Sales Grouped X' table.

[tsdick]

Sorry, measure text...

Qty Var $'s =

(if(sumx('Total Sales Grouped X','Total Sales Grouped X'[total units pp])=0,(SUMx('Total Sales Grouped X','Total Sales Grouped X'[Price cp])),SUMx('Total Sales Grouped X','Total Sales Grouped X'[Price pP]))) * (sumx('Total Sales Grouped X','Total Sales Grouped X'[Quantity Var]))

Thanks.

[tsdick]

Hello JD, Thanks!  Your suggestion worked for the price var $'s -- I also included the table name w/ the column reference (yes a column and not a measure). 

What do you suggest for changing the Qty Var $'s calc -- a bit more complicated.

[tsdick · Accepted solution]

Thanks JD, I arrived at the same....  Thanks for pointnig out that I overused SUMX.  I will accept as solution.  Thanks again.  

fix Qty Var $'s =

sumx('Total Sales Grouped X',if('Total Sales Grouped X'[total units pp]=0,'Total Sales Grouped X'[Price cp],'Total Sales Grouped X'[Price pP]) * 'Total Sales Grouped X'[Quantity Var])