Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

% Difference between Measures

Hi guys,   im trying to calculate simple way difference between tow rates or even more but I dont find the proper way to do it. Here is the thing:     Id like to calculate differences betw...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Yes, that's correct, I want to know how can I create % differences between sales per each year.

 

I dont know how can I made it, just I know putting each year, look this:

 

Net Sales Current = CALCULATE(SUM([Net Sales]);FILTER('Detalles Fechas';'Detalles Fechas'[Año Fiscal]="FY 2019"))

 

Net Sales LY = CALCULATE(SUM([Net Sales]);FILTER('Detalles Fechas';'Detalles Fechas'[Año Fiscal]="FY 2018"))
 
% Dif Net = IFERROR(([Net Sales Current]-[Net Sales LY])/[Net Sales LY];BLANK())
 
I dont want to put the year in each formul, I would like to get something automatically if it'd be possible..
dax's avatar
dax
Community Support
6 years ago

Hi edumen, 

You could try  to add a year column in data model like below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcotUMDIwNFPSUTIyUIrVgQmYAwUMTZEELIACZsgqLIECpkCBWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"FY YAER" = _t, sales = _t]),
    #"Inserted Text After Delimiter" = Table.AddColumn(Source, "Text After Delimiter", each Text.AfterDelimiter([FY YAER], " "), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text After Delimiter",{{"Text After Delimiter", "year"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"year", Int64.Type}})
in
    #"Changed Type"

 Then create a measure like below(use filter in measure to refer to previous value)

Measure =
 (
    CALCULATE ( SUM ( T2[sales] ) )
        - CALCULATE (
            SUM ( T2[sales] ),
            FILTER ( ALL ( T2 ), T2[year] = MIN ( T2[year] ) - 1 )
        )
)
    / CALCULATE ( SUM ( T2[sales] ) )

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I dont understand very well about json option but trying to follow your example getting this

     

    % = CALCULATE(sum('Ratios y Metricas'[Importe Neto Rappel Generico]))-CALCULATE(sum('Ratios y Metricas'[Importe Neto Rappel Generico]);FILTER(ALL('Ratios y Metricas'[Año]);'Ratios y Metricas'[Año]=MIN('Ratios y Metricas'[Año])-1))/CALCULATE(SUM('Ratios y Metricas'[Importe Neto Rappel Generico]))
     
    What do I made wrong?
    • dax's avatar
      dax
      Community Support
      6 years ago

      Hi edume,

      Yes, you could use calculated column to create column which is similar to my M code. You problem is that you miss the () in expression. You could try below expression 

      % =
 (
    CALCULATE ( SUM ( 'Ratios y Metricas'[Importe Neto Rappel Generico] ) )
        - CALCULATE (
            SUM ( 'Ratios y Metricas'[Importe Neto Rappel Generico] );
            FILTER (
                ALL ( 'Ratios y Metricas'[Año] );
                'Ratios y Metricas'[Año]
                    = MIN ( 'Ratios y Metricas'[Año] ) - 1
            )
        )
)
    / CALCULATE ( SUM ( 'Ratios y Metricas'[Importe Neto Rappel Generico] ) )

      Best Regards,
      Zoe Zhi

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

         

         

        It doenst work

         

        being honest I dont understand how something seems so easy such as "% differences between figures" finally is so complex to do.