Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 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
    4 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
    4 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])
Anonymous's avatar
Anonymous
Not applicable
4 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
4 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])