Forum Discussion
Convert static calculated column to dynamic measure
Here is an example of my data:
I need a measure that will for each month+day combination, divide the distinct count of the year column based on my filter selection.
So results would look like this:
The result is the years I would select in my filter so 2016 would divide by 1, 2016 and 2017 would divide by 2, etc...
I have a calculated column that does this calculation correctly, but it's not dynamic based on filter selections:
Avg Amount = DIVIDE('Table'[Amount],CALCULATE(DISTINCTCOUNT('Table'[Year]),ALLEXCEPT('Table','Table'[Month + Day])),0)
So I would need help converting this to a dynamic measure.
5 Replies
- zaza
Resolver III
To make your calculated column into a measure you need to make sure that all elements can be expressed as a single value. So that means wrapping 'Table'[Amount] into an aggregation function like SUM/MAX/AVG.
Avg Amount = DIVIDE(SUM('Table'[Amount]),CALCULATE(DISTINCTCOUNT('Table'[Year]),ALLEXCEPT('Table','Table'[Month + Day])),0) 
v-lili6-msftCommunity Support
hi Anonymous
Just use ALLSELECTED Funtion to create a measrue as below:
New Avg Amount = DIVIDE(SUM('Table'[Amount]), CALCULATE(DISTINCTCOUNT('Table'[Year]),ALLSELECTED('Table')),0)Regards,
Lin
v-lili6-msft So I guess my question isn't really the measure, but the sum of my measure in the matrix is wrong.
1C = DISTINCTCOUNT('Table'[Year])1D = DIVIDE(SUM('Table'[Amount]),[1C],0)So the amounts by row for 1D are correct, but they don't sum together. I understand based on this post, I have to essentially create two calculations, one for each row and one for the total row: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
I'm struggling with the total amount calculation. What it would need to do by each row is take the amount, and then divide that amount by a distinct count of the year column based on that row's Month + Day value. The total column would need to sum each row based on that calculation, but not sure how to put that together.
- v-lili6-msft
hi Anonymous
Yes, it is a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907For your case, you may try create two new measure as below:
Measure 1=SUMX(VALUES('Table' [Business]),[1C])
and
Measure 2=SUMX(VALUES('Table' [Business]),[2D])
Regards,
Lin
