Average Price Increase over each category

[yannaing]

Hi everyone, I have the problems to write a DAX measure about calculating the Average Price Increase for each category, Only after 11/1/2022 and for GOOD Condition Items.
The data sample is as below and order by date.

Date	Type	Cost	Condition
10/27/2022	Desk-1	2700	Good
10/27/2022	Chair-1	1800	Bad
10/27/2022	Table-1	3400	Good
11/9/2022	Desk-1	3000	Bad
11/9/2022	Chair-1	2000	Good
11/9/2022	Chair-1	2000	Good
11/9/2022	Table-1	3500	Good
11/9/2022	Table-1	3500	Good
11/10/2022	Desk-1	3000	Good
11/10/2022	Chair-1	2100	Bad
11/10/2022	Table-1	3500	Bad
11/11/2022	Desk-1	3000	Bad
11/11/2022	Chair-1	2100	Good
11/11/2022	Table-1	3500	Bad
11/12/2022	Desk-1	3000	Good
11/12/2022	Chair-1	2500	Bad
11/12/2022	Table-1	3600	Good
11/12/2022	Table-1	3600	Good

By summarizing table, we can get this table.

	Last Cost	First Cost	Increase Rate
Desk-1	3000	3000	0.00%
Chair-1	2100	2000	5.00%
Table-1	3600	3500	2.86%

Increased Rate formula that I am using is DIVIDE( [Last Cost] - [First Cost], [First Cost] )

-----------------

I have two questions:

• When averaging the Increase rate, should I include the 0% increased or exclude ?
  • if include, (0+25+2.86)/3 = 9.29%
  • if exclude, (25+2.86)/2 = 13.93%
• in both condition (include/exclude), how can I write the DAX for this?

I have tried with the below formula by adding FILTER() conditions, it works when I use the Table visual, but the Total at the bottom of the Table is wrong (meaning it is not averaging) and not working well when using Card visual.

Increase Rate =
      var Latest = MAXX( FILTER(Table, 'Table'[Date] = MAX('Table'[Date]) ), 'Table'[Cost] )
      var Earliest = MINX( FILTER(Table, 'Table'[Date] = MIN('Table'[Date]) ), 'Table'[Cost] )
      return
            AVERAGEX(
                  DISTINCT(Table[Type]),
                  DIVIDE( Latest - Earliest, Earliest)
            )

Could you provide some of the explanations and measures for this question?

Thank you.

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your reply.

Please check the attached pbix file.

I changed the source table and measures.

In my measures, I used the fixed date that indicates 2022,11,1. If you want to change this to dynamically response per month or per year, please let me know with more detailed data.

Thank you.

[Jihwan_Kim]

Hi,

In my opinion, including zero result is better to show all types trend. However, it highly depends on how the industry deals with it, and what is the request from the report consumers.

On each row in the table visualization, filter context is defined as one type per row.

However, on the total row in the table visualization, filter context is defined as all types, so the calculation is using different filter. In my sample attached pbix file, I fixed a measure to show a correct number.

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

[yannaing]

Thank you for your help @Jihwan_Kim. It helps a lot for me.

But I still having other question.

Actually, I left some of the columns/row requirements in the questions because I thought it is not much important. But now I want to include if you can help.

This is the sample data and I want to analyze the Increase rate after 11/1/2022 for the GOOD Condition item only.
Remark - there may be duplicate row for each item within a day. But I still want to get one value for each Item type.

Date	Type	Cost	Condition
10/27/2022	Desk-1	2700	Good
10/27/2022	Chair-1	1800	Bad
10/27/2022	Table-1	3400	Good
11/9/2022	Desk-1	3000	Bad
11/9/2022	Chair-1	2000	Good
11/9/2022	Table-1	3500	Good
11/10/2022	Desk-1	3000	Good
11/10/2022	Chair-1	2100	Bad
11/10/2022	Table-1	3500	Bad
11/11/2022	Desk-1	3000	Bad
11/11/2022	Chair-1	2100	Good
11/11/2022	Table-1	3500	Bad
11/12/2022	Desk-1	3000	Good
11/12/2022	Chair-1	2500	Bad
11/12/2022	Table-1	3600	Good

So the summarizing table would be like this.

	Last Cost	First Cost	Increase Rate
Desk-1	3000	3000	0.00%
Chair-1	2100	2000	5.00%
Table-1	3600	3500	2.86%

Do you still have some suggestions for this requirements?

I tried with your provided file by adding some filter and did not work.

Thanks in advance.

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your reply.

Please check the attached pbix file.

I changed the source table and measures.

In my measures, I used the fixed date that indicates 2022,11,1. If you want to change this to dynamically response per month or per year, please let me know with more detailed data.

Thank you.

[yannaing · Accepted solution]

Thank you for your help @Jihwan_Kim. It helps a lot and I got what I need.

I change Cost measure: = SUM( Data[Cost] ) to Cost measure: = AVERAGE( Data[Cost] )

This is because if I have the multi-row for the same date, it is aggregating. That's why I changed it.

Thank you again and reply appreciate your patience.

[yannaing]

@Jihwan_Kim Please let me ask the follow up questions.

Your solution really works well. I want to know why we get the different results when using VAR in the single measures. I try with this and got the different result.

Increase Rate without 0 =

    var First =

        CALCULATE(

            FIRSTNONBLANKVALUE( 'Calendar'[Date], [Cost measure:] ),

            'Calendar'[Date] >= DATE ( 2022, 11, 1 ),

            Data[Condition] = "Good"

        )

    var Last =

        CALCULATE(

            LASTNONBLANKVALUE( 'Calendar'[Date], [Cost measure:] ),

            'Calendar'[Date] >= DATE ( 2022, 11, 1 ),

            Data[Condition] = "Good"

        )

    return

        AVERAGEX (

            FILTER (

                ADDCOLUMNS (

                    DISTINCT ( 'Type'[Type] ),

                    "@rate", DIVIDE ( Last - First, First )

                ),

                [@rate] <> 0

            ),

            [@rate]

        )

Could you share me why this is happening ?

Thanks in advance.

[Jihwan_Kim]

Hi,

In my opinion, VAR in a measure is a constant number/result that does not vary inside another VAR in the same measure.

In this case, if you want to create in this way, please try the below.

Increase Rate without 0 =
AVERAGEX (
    FILTER (
        ADDCOLUMNS (
            DISTINCT ( 'Type'[Type] ),
            "@rate",
                DIVIDE (
                    CALCULATE (
                        LASTNONBLANKVALUE ( 'Calendar'[Date], [Cost measure:] ),
                        'Calendar'[Date] >= DATE ( 2022, 11, 1 ),
                        Data[Condition] = "Good"
                    )
                        - CALCULATE (
                            FIRSTNONBLANKVALUE ( 'Calendar'[Date], [Cost measure:] ),
                            'Calendar'[Date] >= DATE ( 2022, 11, 1 ),
                            Data[Condition] = "Good"
                        ),
                    CALCULATE (
                        FIRSTNONBLANKVALUE ( 'Calendar'[Date], [Cost measure:] ),
                        'Calendar'[Date] >= DATE ( 2022, 11, 1 ),
                        Data[Condition] = "Good"
                    )
                )
        ),
        [@rate] <> 0
    ),
    [@rate]
)

[yannaing]

Thank you for your explanations and I got a lot from this post.