Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to make a weighted average based on the unpivoted data below, grouped by year and by region / year for other graphs.
These are the results I am trying to achieve for grouping by just year
In order to achieve the end result. I believe PowerBI, behind the scenes, would need to see the table collapsed / groupedby like this:
What is the best practice in dax for grouping unpivoted data for calculations?
The steps I attempted were to create all of this using a Measure with variables for the "occurences * pct" and "weight" columns as they are not necessary outside of the calculation. I was unable to successfully get the measure to work.
Hi @Anonymous ,
Here is my sample data which is similar to yours.
Then you can use one measure to get your result.
Measure = VAR a = SELECTEDVALUE ( 'Table'[pct] ) VAR b = SELECTEDVALUE ( 'Table'[occurence] ) VAR c = CALCULATE ( SUMX ( 'Table', 'Table'[pct] * 'Table'[occurence] ) / 2, ALLEXCEPT ( 'Table', 'Table'[year] ) ) RETURN ( a * b ) / c * a
Here is the result.
Hi @Anonymous ,
The data for each line of yours cannot be exactly the same, otherwise it will be aggregated in the visual. If you want to get the result like your second image, you need to add an index column to ensure that each row of data is unique.
@v-eachen-msft Adding an index column still causes the formula to aggregate on a row level and not based on year. What am I missing here? I am so puzzled.
@v-eachen-msft Thank you for your help on this. I have included a sample of the dataset including all columns along with the measure you created to show an example of how it's not working.
weightedAVG.pbix on Google Drive - There's no attachment button for me on the forum.
Hi @Anonymous ,
Could you tell me what percentage of each country should appear in this sample data? Maybe the logic needs to be modified.
The only columns of concern are country, year, pct, and occurence. The remaining columns are only there to show why there are so many repeats when unpivoted.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |