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

 

 

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

6 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    4 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
      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])
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago
      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.
  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    4 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.

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