Forum Discussion

zebra's avatar
zebra
Icon for Helper II rankHelper II
2 years ago
Solved

Create 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 col...
Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    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's avatar
Fowmy
Icon for Super User rankSuper User
2 years ago

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] )


 



 



  • zebra's avatar
    zebra
    Icon for Helper II rankHelper II
    2 years ago

    Fowmy , 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?

    • Fowmy's avatar
      Fowmy
      Icon for Super User rankSuper User
      2 years ago

      zebra 

      Please check the attached file:

      Norm.Dist = 

VAR __Age = ALLSELECTED( Sheet1[age] )
VAR __Mean = AVERAGEX( __Age , Sheet1[age] )
VAR __StdDev = STDEVX.S( __Age , Sheet1[age] )
RETURN
	  NORM.DIST( MAX( Sheet1[Age]) ,__Mean  ,__StdDev,FALSE())

       

       



      • zebra's avatar
        zebra
        Icon for Helper II rankHelper II
        2 years ago

        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.

         

        https://community.fabric.microsoft.com/t5/Desktop/Calculating-Normal-Distribution-based-on-selected-values-slicer/m-p/3606269

         

        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