Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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 ...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    5 years ago

    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]
)

     

  • Anonymous's avatar
    Anonymous
    5 years ago

    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])
jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
5 years ago

Anonymous 

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.

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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])