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
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.

       

       

       

       

      • dax's avatar
        dax
        Community Support
        6 years ago

        Hi edumen, 

        It seems that it is cuased by your sample structure, so if possible, could you please inform me your sample data or your pbix file? Then I will help you more correctly.

        Please do mask sensitive data before uploading.

        Thanks for your understanding and support.
        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.