Forum Discussion
Create a measure or dynamic virtual table based on slicer selection that returns table
zebra
I've adjusted the calculation. Kindly review the attached file. The chart now aligns with Barcelona as expected in your results. I utilized MAX to capture the current value. The choice of MIN, SUM, or AVG would also suffice since we're dealing with a single value. The crucial aspect is employing an aggregation function to obtain the filter context value.Below is the DAX calculation for NORM.DIST. It now specifically takes into account the current selection by using ALLSELECTED.
Norm.Dist = VAR __Mean = CALCULATE( AVERAGE(Sheet1[Age]) , ALLSELECTED( Sheet1 )) VAR __StdDev = CALCULATE( STDEV.S(Sheet1[Age]) , ALLSELECTED( Sheet1 )) VAR __Result = NORM.DIST( AVERAGE( Sheet1[Age]) ,__Mean ,__StdDev,FALSE()) RETURN __Result
Fowmy , thanks for your solution, however this is not correct. I said this becuase you are calculating average and standard deviation for whole dataset however we need to calculate it based on subset of data. this can be verified by looking at the barcelona graph which is as follow
it should not be like this. if you create another sheet that contains only barcelona data then you will see the correct graph.
please see the following post how this graph should look like.
in your formula, we only need to select that ages according to slicer selection and then calculate the avereage and std for the selected dataset. one more question why do you use max function in the return statement?
thanks
zeebee
zebra
I've adjusted the calculation. Kindly review the attached file. The chart now aligns with Barcelona as expected in your results. I utilized MAX to capture the current value. The choice of MIN, SUM, or AVG would also suffice since we're dealing with a single value. The crucial aspect is employing an aggregation function to obtain the filter context value.
Below is the DAX calculation for NORM.DIST. It now specifically takes into account the current selection by using ALLSELECTED.
Norm.Dist =
VAR __Mean = CALCULATE( AVERAGE(Sheet1[Age]) , ALLSELECTED( Sheet1 ))
VAR __StdDev = CALCULATE( STDEV.S(Sheet1[Age]) , ALLSELECTED( Sheet1 ))
VAR __Result = NORM.DIST( AVERAGE( Sheet1[Age]) ,__Mean ,__StdDev,FALSE())
RETURN
__Result
thanks Fowmy , this is exactly I was looking for. I am asking few questions just for my knowledge in the following code.
Norm.Dist = VAR __Mean = CALCULATE( AVERAGE(Sheet1[Age]) , ALLSELECTED( Sheet1 )) VAR __StdDev = CALCULATE( STDEV.S(Sheet1[Age]) , ALLSELECTED( Sheet1 )) VAR __Result = NORM.DIST( AVERAGE( Sheet1[Age]) ,__Mean ,__StdDev,FALSE()) RETURN __Result
I understand the ALLSELECTED is used to select the current selection from sheet1 in calculating mean and std dev. however in calculating NORM.DIST(AVERAGE(Sheet[Age]) , how we are restricting here to use only those points that are currently selected? I knew that we are applying this funtion to a single value however how this single value is from the selected list?
Hope I explain the question well.
thanks
zeebee
zebra
Appreceae it!
In response to your query about employing AVERAGE on the Age column, it's crucial to note that the NORM.DIST function requires a scalar value as its first parameter, representing the value for which the distribution is sought. The X-Axis, denoting age, is already filtered based on your canvas selection, such as Spain>Barcelona. By utilizing AVERAGE, I've chosen the present value from this filtered X-Axis. Alternatively, you can opt for any other aggregate function. I trust this clarifies the concept.
You have also posted the this question using a different thread, I can post the same answer their as well and could you accept it as it will be helpful for those who visit that question.
Re: Calculating Normal Distribution based on selec... - Microsoft Fabric Communitysure. please post and i will accept it as a solution.