topic Re: Weighted Average Based on Multiple Critera and Filters in DAX Commands and Tips

Weighted Average Based on Multiple Critera and Filters

JustinShultz — Fri, 12 Jun 2020 16:08:08 GMT

Hello Power Bi Community,

I am new to Power Bi and trying to understand how to do a fairly complex DAX calculation. I have several weighted averages I would like to calculate but the denomanator is changing based on several criteria. Heres a short snippet of my example.

Project Name	Sector	Reporting Year	% Savings	Area
Project 1	HE	2019	50	100000
Project 2	GO	2019	32	50000
Project 3	GO	2019	43	75000
Project 4	HE	2019	28	35000
Project 1	HE	2020	53	100000
Project 2	GO	2020	36	50000
Project 3	GO	2020	45	75000
Project 4	HE	2020	28	35000

The numbers I am trying to calucalte include:

Area Weighted % Savings by Reporting Year = % Saving * Area / SUM(Area by Reporting Year)

Area Weighted % Savings by Reporting Year and Sector = % Savings * Area / SUM(Area by Reporting Year and Sector)

My method so far as been to create individual columns for each of these steps.

Area by Year = CALCULATE(SUM('Table'[Area]), ALLEXCEPT('Table','Table'[Reporting Year]))

Which creates a column of the summed area for the same year of the Reporting Year
I tried using a measure and then having the sum filtered on the Report tab based on removing all other Reporting Years but this did not change the SUM.

I then setup Area by Reporting Year and Sector, one per column:

HE Area by Year = CALCULATE(
SUM('Table'[Area]), 
ALLEXCEPT('Table','Table'[Reporting Year]), 
'Table'[Sector]="HE")

This step was repeated for each of the different sectors and has become quite tedious and difficult to manage when creating graphics on the Report tab.

I then use each of these in the denomenator of the Area Weighted % Savings equation. With the number calculated I'm creating visualizations in the Report tab and trying to filter by Reporting Year and Sector.

I believe there is some way I can get the SUM Total Area by Report Year, Sector, and other criteria without creating so many columns. Is there an easier way to do this with a DAX equation?

Re: Weighted Average Based on Multiple Critera and Filters

lbendlin — Fri, 12 Jun 2020 19:57:23 GMT

You don't really want to use columns for that. Transform them to measures.

When you create your DAX formula, use copious amounts of variables to validate that your intermediate steps do what you want. Since in DAX everything is a table (just sometimes a single row/single column table), use CONCATENATEX to visualize your intermediate results.

An example for the first measure would be

% by year =

var y = selectedvalue('Table'[Reporting Year])

var s = SELECTEDVALUE('Table'[% Savings])*SELECTEDVALUE('Table'[Area])

var t = CALCULATE(sum('Table'[Area]),Filter(ALL('Table'),'Table'[Reporting Year]=y))

var r = divide(s,t)

return r

This is a bit excessive but it demonstrates the general approach.

Re: Weighted Average Based on Multiple Critera and Filters

JustinShultz — Mon, 15 Jun 2020 15:43:01 GMT

Hello lbendlin, thank you for your reply. I was not aware that you can use var to hold variable information. I have two follow up questions to expand my understanding for more applications.

How would I expand the FILTER() or other functions to include additional critera? For example, while keeping the year filter, I can then add a filter for the sector like "HE" or "GO"? I would like to do another weighted average based on each year and each sector.
I am having trouble using the measure to visualize values over multiple years. I am creating a scatter plot but the measure only displays the last year of results, instead of the result for each year. Even when using a visualization filter, results are not shown for 2019, only 2020. How do I display the measure results spread out for each year in a scatter plot?

Thank you for taking the time to help me with my beginner questions.

Re: Weighted Average Based on Multiple Critera and Filters

lbendlin — Mon, 15 Jun 2020 15:57:07 GMT

1. yes, CALCULATE() and many other functions accept multiple filters that are ANDed together. You can als use &&, || and IN for more elaborate filters (at a storage engine performance cost). Check the documentation for detailed syntax.

2. "it depends" - do you want the year as an attribute in the scatter, or do you want to ignore it?

Re: Weighted Average Based on Multiple Critera and Filters

JustinShultz — Mon, 15 Jun 2020 16:11:48 GMT

2. I would like the year as an attribute. I would like to display the results for the measure based on the results of year [Reporting Year]. So the % of Year measure is on the Y-axis and Reporting Year is on the X-axis.

Re: Weighted Average Based on Multiple Critera and Filters

lbendlin — Mon, 15 Jun 2020 16:34:28 GMT

That would give you a scatter plot with vertical columns of dots for each year. Are you sure you want that?

Re: Weighted Average Based on Multiple Critera and Filters

JustinShultz — Mon, 15 Jun 2020 16:58:48 GMT

I believe I mis-understand then. Below are two images of what I'm working with.

The first is close to what I want but the orange dot is the measure we created, notice it's only on 2021.

The second graphic is the measure on a scatter plot by itself.