Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

wrong column total for calculate measure

hi

i have a measure

calculate by this formula 

MixCalc:=iferror(
(
([Avg CY]-[Avg LY])
*( divide( [amountLY],(CALCULATE( [amountLY],allselected(), values(BYBRANDCOLLCHMKT[Brand]))) ) )),0)

 

and the table data is like this source Table

into analysis i put on row the fields (brand, channel, market)

 

the AVG LY and AVG CY are measure like this:

avg LY:=sum([amountLY])/sum([piecesLY])

 

when i create the CalcMeasure, the total on column is WRONG , i try to use SUMX(values(TABLE[brand]),[MixCalc]) to get correct column sum

but seems not work

 

someone can help me?

thanks a lot

Need Help
Tips and Tricks

7 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Hi Anonymous

     

    I am not sure... but please give it a shot.. Create a new MEASURE

     

    Measure =
IF (
    HASONEVALUE ( TABLE[brand] ),
    [MixCalc],
    SUMX ( ALL ( TABLE[brand], Table[Channel], Table[Market] ), [MixCalc] )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      hi Zubair_Muhammad

      thanks a lot for your reply 

      i'm going to show you the detail of the table  

      this is the table 

       

      the delta_sconto_market measure work fine but it has wrong column total, let me show the measure

       

      DELTA_Sconto_Market:=iferror(
      (
      ([Avg Discc CY]-[Avg Discc PY])
      *( divide(   [GS CY],(CALCULATE( [GS CY],allselected(), values(BYBRANDCOLLCHMKT[BRANDCOLL]))) )  )),0)

       

      the [Avg Discc CY] and [Avg Discc PY] are two measure calucalte at row level by measure lake this

       

      Avg Discc CY:=(IFERROR([NS CY]/[GS CY]-1,0))

       

      your solution seems work only when there is one row at [market] level (leaf level) (first case) 

       

      but in second case your formula not works

       

      do i use a wrong method to get value at different row level i mean (CALCULATE( [GS CY],allselected(), values(BYBRANDCOLLCHMKT[BRANDCOLL])))) 

      or it is right ?

       

       

      thanks a lot

       

       

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Hi Anonymous

         

        Try replacing HASONEVALUE with ISFILTERED...

         

        Measure =
IF (
    ISFILTERED ( TABLE[brand] ),
    [MixCalc],
    SUMX ( ALL ( TABLE[brand], Table[Channel], Table[Market] ), [MixCalc] )
)