Forum Discussion

alamhaz's avatar
alamhaz
Helper I
4 years ago
Solved

SumX is giving wrong value

Hi 

I am calcultaing sum of the measure but its giving me wrong value, all meaure gives wrong value on row lelvel but one measure give me correct value when i filter with product, 

What i am doing is multplying total qty diff avg with avg selling price , i am getting correct result in Avg sale lost but its not giving total measure is 

IF([Total Qty Diff Avg] >0, [Total Qty Diff Avg] * [Avg Selling Price (PV)] ,BLANK())
after comupting this i am computing with sumx measure is 
Avg Sale lost (product ) = SUMX(VALUES('Product Movement'[Date]), [Avg Sale Lost]) and 
Avg Sale lost (total ) = SUMX(VALUES('Product Movement'[Prod Key Value]), [Avg Sale Lost])
both measure is not giving correct result even i tried in calculted column still getting wrong value.
Check the Screen shot 
Need Help
Tutorial Requests
  • alamhaz's avatar
    alamhaz
    4 years ago

    i figure out the solution , i am posting it may be someone needs. 

    i change the measure as follow simply add the hasonfilter with my measure,

    Avg Sale lost (product ) = IF(HASONEVALUE('Product Movement'[Date]),[Avg Sale Lost], BLANK())
    then i use this measure 
    Avg sale los f = if(HASONEVALUE('Product Movement'[Date])=TRUE(), [Avg Sale lost (product )],sumx(SUMMARIZE('Product Movement','Product Movement'[Product Name],'Product Movement'[Date]),[Avg Sale lost (product )]))
    here i am removing filter on total and subtotal. you can read this belwo artical for more details 
     
     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    alamhaz , what is correct level based on visual ?

     

    try like

    SUMX(summarize('Product Movement','Product Movement'[Date],'Product Movement'[Prod Key Value],"_1", [Avg Sale Lost]),[_1])

  • alamhaz's avatar
    alamhaz
    Helper I
    4 years ago

    i figure out the solution , i am posting it may be someone needs. 

    i change the measure as follow simply add the hasonfilter with my measure,

    Avg Sale lost (product ) = IF(HASONEVALUE('Product Movement'[Date]),[Avg Sale Lost], BLANK())
    then i use this measure 
    Avg sale los f = if(HASONEVALUE('Product Movement'[Date])=TRUE(), [Avg Sale lost (product )],sumx(SUMMARIZE('Product Movement','Product Movement'[Product Name],'Product Movement'[Date]),[Avg Sale lost (product )]))
    here i am removing filter on total and subtotal. you can read this belwo artical for more details