Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

SUMX wrong totals

HI

when I export data, totals are different to what i am getting in Power bi dashboard

please help me with the following problem.

I have customer, material , segmant, sales, quantitiy all the fields in one sales table

I want to calculate price variance 

(current year sales/ quantity -previous year sales/quantity) * current year quantitiy. but able to see with all the attributes.. 

if there are no sales for current or previous year on a particular material it has to be 0 otherwise above formula.

 

below

 

Price Variance =
if([Sales per Unit_CY]=0 || [Sales per Unit_PY]=0,0,
 
   CALCULATE(SUMX(VALUES(ATC[material]),[Sales_Per_unit_var]*[Quantity_CY]),ALLSELECTED(ATC[customer])))

 

Need Help
Show and Tell

6 Replies

  • Wilson_'s avatar
    Wilson_
    Icon for Memorable Member rankMemorable Member
    2 years ago

    Hi giri,

     

    Please check the pinned thread in the forum to learn how to provide sufficient details to get your question answered. 🙂

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Share some data, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    my table values are customer no, material no,  sales amount, Quantity ,year month.
    calculation require :


    if the sales current year or sales previous year sales is nill  then 0 if not
    ==sales per unit variance( current sales /quantity - previous year sales /quantity) * quantity cy 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    sales has to be calculated to material level hence I have used 

    Price Variance =
    if([Sales per Unit_CY]=0 || [Sales per Unit_PY]=0,0,
     
       CALCULATE(SUMX(VALUES(ATC[material_style1]),[Sales_Per_unit_var]*[Quantity_CY]),ALLSELECTED(ATC[top_level_account])))
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    they are not helpful, its simple by totals not adding up . I think its context transition issue. 

    values are correct , but total of correct values not correct .

     

     

    if([Sales per Unit_CY]=0 || [Sales per Unit_PY]=0,0,
     
       CALCULATE(SUMX(VALUES(ATC[material_style1]),[Sales_Per_unit_var]*[Quantity_CY]),ALLSELECTED(ATC[top_level_account])))