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 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.