wrong column total for calculate measure

[Anonymous]

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

[Zubair_Muhammad]

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]

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]

Hi @Anonymous

Try replacing HASONEVALUE with ISFILTERED...

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

[Anonymous]

seems that not work

your formula (traslated to the correct column name) is

test:=IF (

    ISFILTERED ( BYBRANDCOLLCHMKT[BRANDCOLL] ),
    [DELTA_Sconto_Market],
    SUMX ( ALL ( BYBRANDCOLLCHMKT[BRANDCOLL], BYBRANDCOLLCHMKT[ACTUALCHANNEL], BYBRANDCOLLCHMKT[Market] ),  [DELTA_Sconto_Market] )
)

my source formula of measure

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

seems that works fine there are only one row at the leaf level

if you need other detail let me know

thanks a lot 

for your help

[Zubair_Muhammad]

@Anonymous

Could you share your file???

[Anonymous]

of course
i send you by email the
entire file

thanks a lot

[Anonymous]

i attach the original excel which has the issue at onedrive link

you could download it at File Excel

the "mix market" and "delta sconto" have a wrong column sum (i'd like to have a simply sum, not a ricalculation at parent level)