Display of Aggregated Value instead of Averaged Value

[Anonymous]

Hi Everyone,

I am creating a powerbi report to illustrate difference between forecasted value and actual sales. One of the measure is the percentage difference between these two value. Following is the sample data.

SKU code	Actual Sales M1	Actual Sales M2	Actual Sales M3	Forecast M1	Forecast  M2	Forecast M3	Total sales	Difference	% Difference
SKU 1	100	100	100	400	400	400	300	-300	-300%
SKU 2	200	200	200	500	500	500	600	-300	-150%
SKU 3	300	300	300	600	600	600	900	-300	-100%

Percentage Difference is Difference/(Total sales/3)

I would like to show the overall value ,ie. total difference/ (total sales/ 3)= -150% in the sample, but only averaged value, i.e. -183% is shown even if I created a measure using the above formula. Much grateful if a solution can be provided to show the overall value. Thanks!

[DataInsights · Accepted solution]

@Anonymous,

The first step is transforming the data. Here's an example in Power Query:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WOrQg2DtUwfDQAiUdJUMDg0MLcLJM8LBidWAmGYHFjeAqsLFM8bAQJhmDxY3hKrCxzOCsQwvMMERjYwE=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        #"SKU code" = _t,
        #"Actual Sales M1" = _t,
        #"Actual Sales M2" = _t,
        #"Actual Sales M3" = _t,
        #"Forecast M1" = _t,
        #"Forecast M2" = _t,
        #"Forecast M3" = _t
      ]
  ),
  ChangeType = Table.TransformColumnTypes(
    Source,
    {
      {"SKU code", type text},
      {"Actual Sales M1", Int64.Type},
      {"Actual Sales M2", Int64.Type},
      {"Actual Sales M3", Int64.Type},
      {"Forecast M1", Int64.Type},
      {"Forecast M2", Int64.Type},
      {"Forecast M3", Int64.Type}
    }
  ),
  UnpivotColumns = Table.UnpivotOtherColumns(ChangeType, {"SKU code"}, "Attribute", "Value"),
  SplitColumn = Table.SplitColumn(
    UnpivotColumns,
    "Attribute",
    Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true),
    {"Attribute.1", "Attribute.2"}
  ),
  RenameColumns = Table.RenameColumns(
    SplitColumn,
    {{"Attribute.1", "Type"}, {"Attribute.2", "Month"}}
  )
in
  RenameColumns

Transformed data:

The measures should flow more easily now. Let me know if you have any questions.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

As is suggested by @DataInsights , you may transform your data as below in Power Query. The pbix file is attached in the end.

Table:

Then you may create the following measure.

Re = 
IF(
    ISINSCOPE('Table'[SKU code]),
    IF(
        ISINSCOPE('Table'[M]),
        SUM('Table'[Value]),
        IF(
            ISINSCOPE('Table'[Actual/Forecast]),
            SUM('Table'[Value]),
            DIVIDE(
                CALCULATE(
                    AVERAGE('Table'[Value]),
                    FILTER(
                        ALLEXCEPT('Table','Table'[SKU code]),
                        [Actual/Forecast]="Actual"
                    )
                )-
                CALCULATE(
                    AVERAGE('Table'[Value]),
                    FILTER(
                        ALLEXCEPT('Table','Table'[SKU code]),
                        [Actual/Forecast]="Forecast"
                    )
                ),
                CALCULATE(
                    SUM('Table'[Value]),
                    FILTER(
                        ALLEXCEPT('Table','Table'[SKU code]),
                        [Actual/Forecast]="Actual"
                    )
                ),
                0
            )*3
        )
    ),
    IF(
        ISINSCOPE('Table'[Actual/Forecast]),
        SUM('Table'[Value]),
        SUMX(
            SUMMARIZE(
                ALL('Table'),
                'Table'[SKU code],
                "Result",
                DIVIDE(
                    CALCULATE(
                        AVERAGE('Table'[Value]),
                        FILTER(
                            ALL('Table'),
                            [SKU code]=EARLIER('Table'[SKU code])&&
                            [Actual/Forecast]="Actual"
                        )
                    )-
                    CALCULATE(
                        AVERAGE('Table'[Value]),
                        FILTER(
                            ALL('Table'),
                            [SKU code]=EARLIER('Table'[SKU code])&&
                            [Actual/Forecast]="Forecast"
                        )
                    ),
                    CALCULATE(
                        SUM('Table'[Value]),
                        FILTER(
                            ALL('Table'),
                            [SKU code]=EARLIER('Table'[SKU code])&&
                            [Actual/Forecast]="Actual"
                        )
                    ),
                    0
                )*3
            ),
            [Result]
        )
    )
)

Result:

Best Regards

Allan

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

[Syndicate_Admin · Accepted solution]

Hi, @dt1119 

As is suggested by @DataInsights , you may transform your data as below in Power Query. The pbix file is attached in the end.

Table:

Then you may create the following measure.

Re = 
IF(
    ISINSCOPE('Table'[SKU code]),
    IF(
        ISINSCOPE('Table'[M]),
        SUM('Table'[Value]),
        IF(
            ISINSCOPE('Table'[Actual/Forecast]),
            SUM('Table'[Value]),
            DIVIDE(
                CALCULATE(
                    AVERAGE('Table'[Value]),
                    FILTER(
                        ALLEXCEPT('Table','Table'[SKU code]),
                        [Actual/Forecast]="Actual"
                    )
                )-
                CALCULATE(
                    AVERAGE('Table'[Value]),
                    FILTER(
                        ALLEXCEPT('Table','Table'[SKU code]),
                        [Actual/Forecast]="Forecast"
                    )
                ),
                CALCULATE(
                    SUM('Table'[Value]),
                    FILTER(
                        ALLEXCEPT('Table','Table'[SKU code]),
                        [Actual/Forecast]="Actual"
                    )
                ),
                0
            )*3
        )
    ),
    IF(
        ISINSCOPE('Table'[Actual/Forecast]),
        SUM('Table'[Value]),
        SUMX(
            SUMMARIZE(
                ALL('Table'),
                'Table'[SKU code],
                "Result",
                DIVIDE(
                    CALCULATE(
                        AVERAGE('Table'[Value]),
                        FILTER(
                            ALL('Table'),
                            [SKU code]=EARLIER('Table'[SKU code])&&
                            [Actual/Forecast]="Actual"
                        )
                    )-
                    CALCULATE(
                        AVERAGE('Table'[Value]),
                        FILTER(
                            ALL('Table'),
                            [SKU code]=EARLIER('Table'[SKU code])&&
                            [Actual/Forecast]="Forecast"
                        )
                    ),
                    CALCULATE(
                        SUM('Table'[Value]),
                        FILTER(
                            ALL('Table'),
                            [SKU code]=EARLIER('Table'[SKU code])&&
                            [Actual/Forecast]="Actual"
                        )
                    ),
                    0
                )*3
            ),
            [Result]
        )
    )
)

Result:

Best Regards

Allan

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

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

As is suggested by @DataInsights , you may transform your data as below in Power Query. The pbix file is attached in the end.

Table:

Then you may create the following measure.

Re = 
IF(
    ISINSCOPE('Table'[SKU code]),
    IF(
        ISINSCOPE('Table'[M]),
        SUM('Table'[Value]),
        IF(
            ISINSCOPE('Table'[Actual/Forecast]),
            SUM('Table'[Value]),
            DIVIDE(
                CALCULATE(
                    AVERAGE('Table'[Value]),
                    FILTER(
                        ALLEXCEPT('Table','Table'[SKU code]),
                        [Actual/Forecast]="Actual"
                    )
                )-
                CALCULATE(
                    AVERAGE('Table'[Value]),
                    FILTER(
                        ALLEXCEPT('Table','Table'[SKU code]),
                        [Actual/Forecast]="Forecast"
                    )
                ),
                CALCULATE(
                    SUM('Table'[Value]),
                    FILTER(
                        ALLEXCEPT('Table','Table'[SKU code]),
                        [Actual/Forecast]="Actual"
                    )
                ),
                0
            )*3
        )
    ),
    IF(
        ISINSCOPE('Table'[Actual/Forecast]),
        SUM('Table'[Value]),
        SUMX(
            SUMMARIZE(
                ALL('Table'),
                'Table'[SKU code],
                "Result",
                DIVIDE(
                    CALCULATE(
                        AVERAGE('Table'[Value]),
                        FILTER(
                            ALL('Table'),
                            [SKU code]=EARLIER('Table'[SKU code])&&
                            [Actual/Forecast]="Actual"
                        )
                    )-
                    CALCULATE(
                        AVERAGE('Table'[Value]),
                        FILTER(
                            ALL('Table'),
                            [SKU code]=EARLIER('Table'[SKU code])&&
                            [Actual/Forecast]="Forecast"
                        )
                    ),
                    CALCULATE(
                        SUM('Table'[Value]),
                        FILTER(
                            ALL('Table'),
                            [SKU code]=EARLIER('Table'[SKU code])&&
                            [Actual/Forecast]="Actual"
                        )
                    ),
                    0
                )*3
            ),
            [Result]
        )
    )
)

Result:

Best Regards

Allan

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

[DataInsights · Accepted solution]

@Anonymous,

The first step is transforming the data. Here's an example in Power Query:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WOrQg2DtUwfDQAiUdJUMDg0MLcLJM8LBidWAmGYHFjeAqsLFM8bAQJhmDxY3hKrCxzOCsQwvMMERjYwE=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        #"SKU code" = _t,
        #"Actual Sales M1" = _t,
        #"Actual Sales M2" = _t,
        #"Actual Sales M3" = _t,
        #"Forecast M1" = _t,
        #"Forecast M2" = _t,
        #"Forecast M3" = _t
      ]
  ),
  ChangeType = Table.TransformColumnTypes(
    Source,
    {
      {"SKU code", type text},
      {"Actual Sales M1", Int64.Type},
      {"Actual Sales M2", Int64.Type},
      {"Actual Sales M3", Int64.Type},
      {"Forecast M1", Int64.Type},
      {"Forecast M2", Int64.Type},
      {"Forecast M3", Int64.Type}
    }
  ),
  UnpivotColumns = Table.UnpivotOtherColumns(ChangeType, {"SKU code"}, "Attribute", "Value"),
  SplitColumn = Table.SplitColumn(
    UnpivotColumns,
    "Attribute",
    Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true),
    {"Attribute.1", "Attribute.2"}
  ),
  RenameColumns = Table.RenameColumns(
    SplitColumn,
    {{"Attribute.1", "Type"}, {"Attribute.2", "Month"}}
  )
in
  RenameColumns

Transformed data:

The measures should flow more easily now. Let me know if you have any questions.

[Anonymous]

@v-alq-msft @DataInsights  thank you so much for your help!