Forum Discussion
Helper IICreate a measure or dynamic virtual table based on slicer selection that returns table
Hi All,
Would you please help in following scenario?
I have a table that contains ages and city information as follow.
I want to create the normal distribution for the age column. The formula to create normal distribution is as follows
NORM.DIST(X, Mean, Standard_dev, Cumulative)
I created the calculated column to calculate the normal distribution of complete data set and it worked fine however the actual problem arises when I use a filter let’s say city filter. Here the problem is that normal distribution depends on the average and standard deviation, and these calculation will be different for complete dataset and for subset of data. And in that case calculated column would not work as these are static and pre-calculate the normal distribution for the entire dataset.
The other alternative that comes into my mind is create a measure that return a table that consist of subset of data along with its normal distribution value. The measure should return the data depend on slicer value. e.g if we select Barcelona from city slicer then here is what I am expecting to return by that measure.
City | age | n.d |
Barcelona | 0 | 0.002 |
Barcelona | 0 | 0.002 |
Barcelona | 7 | 0.002 |
Barcelona | 33 | 0.001 |
Barcelona | 33 | 0 |
The end goal is to use normal distribution along age in the chart. If this is not possible by measure then would it be possible to create a virtual table?
Any pointers/ help from community would be highly appreciated.
Best
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
13 Replies
- amitchandak
Super User
zebra , Based on what I got,
Can you try if this can help
calculate(NORM.DIST(X, Mean, Standard_dev, Cumulative) , all())
- HarishKM
zebra Hey,
you can try this. NORM.DIST function (DAX) - DAX | Microsoft Learn
and slicer based on measure - What are Field Parameters in Power BI? | phData
Thanks
Harish
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! - Fowmy
zebra
To use any column on a chart, it has to come from your model, virtaul table column cannot be used. But if you want to use the data generated for Normal Distribution and aggregate it, then a measure would work. Check my example below, I created a virtual table of the Norm.Dist but that column has to aggregated to use on a measure.Norm.Dist = VAR __Age = SELECTCOLUMNS( Table20 , Table20[age] ) VAR __Mean = AVERAGE( Table20[age] ) VAR __StdDev = STDEV.S( Table20[age] ) VAR __nd_Table = ADDCOLUMNS( SELECTCOLUMNS( Table20 , Table20[age] ), "@nd" , NORM.DIST( Table20[age] ,__Mean ,__StdDev,FALSE()) ) RETURN MAXX( __nd_Table , [@nd] )
zebraFowmy , I appreiciate your post however as I mentioned above, normal distribution needs to draw on a chart and it needs to be calculated dynamically based on slicer selection, the aggregation is not required. This is quite simple requirement and I am amazed to know that power BI can not handle such basic calculation that is return a non scalar measure to be use by a graph. I believe this feature should be requested if unavailable right now.
any other alternative you can thought of?
