Calculate column after filters applied

[tmrwall]

I'm new to Power BI and I am struggling to plot a calculated ratio based on filters. I would like to calculate the average unit price for the different quarters after filters have been applied to Category.

For example, my dataset looks like this:

I would like the results that are plotted to look like this, if Category <> C filter is applied:

Many thanks in advance!

[DataNinja777 · Accepted solution]

Hi @tmrwall ,

Since your data table doesn't have a date column, create a Start of Quarter Date using DAX in a calculated column:

Start of Quarter Date = 
DATE(
    'Table'[Year],
    right('Table'[Quarter],1) * 3 - 2,
    1
)

Next, to enable proper time-based filtering, create a Calendar Table using DAX:

Calendar =
ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2025, 12, 31)),
    "Year", YEAR([Date]),
    "Quarter", "Q" & FORMAT(CEILING(MONTH([Date]) / 3, 1), "0"),
    "Year-Quarter", FORMAT([Date], "YYYY") & "-Q" & FORMAT(CEILING(MONTH([Date]) / 3, 1), "0")
)

Then, create a relationship between the tables

Go to Model View in Power BI and:

1. Connect the Start of Quarter Date in your Sales table to the Date column in the Calendar Table.
2. Ensure the relationship is one-to-many with the Calendar Table filtering the Sales table.

Now, calculate the Average Unit Price by Quarter using this DAX measure:

Average Unit Price by Quarter =
DIVIDE(
    SUM('Sales'[Amount Paid]),
    SUM('Sales'[Number of units])
)

This measure will dynamically adjust based on the filters applied, such as Category <> C.

I have attached an example pbix file for your reference.

Best regards,

[Anonymous · Accepted solution]

Hi @tmrwall ,

According to your screenshot, I am confused about the [Average Unit Price] after 2023-Q1. I think the [Average Unit Price] should be calculated by dividing [Amount Paid] by [Number of units]. However, in your screenshot, it shows 9 in 2023 Q2.

As far as I know, calculated column couldn't be dynamic.
If you want to get dynamic result by filter, I suggest you to try to create a measure. 

Measure = 
DIVIDE(SUM('Table'[Amount Paid]),SUM('Table'[Number of units]))

Result is as below.

Best Regards,
Rico Zhou

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

[DataNinja777 · Accepted solution]

Hi @tmrwall ,

Since your data table doesn't have a date column, create a Start of Quarter Date using DAX in a calculated column:

Start of Quarter Date = 
DATE(
    'Table'[Year],
    right('Table'[Quarter],1) * 3 - 2,
    1
)

Next, to enable proper time-based filtering, create a Calendar Table using DAX:

Calendar =
ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2025, 12, 31)),
    "Year", YEAR([Date]),
    "Quarter", "Q" & FORMAT(CEILING(MONTH([Date]) / 3, 1), "0"),
    "Year-Quarter", FORMAT([Date], "YYYY") & "-Q" & FORMAT(CEILING(MONTH([Date]) / 3, 1), "0")
)

Then, create a relationship between the tables

Go to Model View in Power BI and:

1. Connect the Start of Quarter Date in your Sales table to the Date column in the Calendar Table.
2. Ensure the relationship is one-to-many with the Calendar Table filtering the Sales table.

Now, calculate the Average Unit Price by Quarter using this DAX measure:

Average Unit Price by Quarter =
DIVIDE(
    SUM('Sales'[Amount Paid]),
    SUM('Sales'[Number of units])
)

This measure will dynamically adjust based on the filters applied, such as Category <> C.

I have attached an example pbix file for your reference.

Best regards,