Create a measure to show AverageX value of two field even we select any one field in the Slicer

[SantoshKumar]

Hi All,

I have created a Measure (Resource_Count) which will do Group By and it is working as expected but i have a requirement for one specific filed (Area3) as follows.
When i select Area3 , irrespective of selected Region (Central or East), it should show the combined value as mentioned below.

Resource_Count = AVERAGEX(SUMMARIZE(SeqTable,SeqTable[TestedDay],

"CalMeasure",DISTINCTCOUNT(SeqTable[Resources])),[CalMeasure])

Sample data: 

Area	Region	Role	TestedDay	Resources
Area1	East	Developer	1	Wenj
Area1	Central	Support	2	George
Area1	Pacific	Tester	3	Ross
Area1	East	Developer	4	Suvarna
Area2	Central	Support	1	Mark
Area2	Pacific	Tester	2	Dev
Area2	East	Developer	3	Mahi
Area3	Central	Support	4	George
Area3	Pacific	Tester	1	Suvarna
Area3	East	Developer	2	Ross
Area3	Central	Support	3	Praj
Area3	Pacific	Tester	4	Tim
Area3	East	Developer	1	Dev
Area3	Central	Tester	2	Mark

Regards
Santosh Kumar 

[v-yueyunzh-msft]

Hi , @SantoshKumar 

According to your description, do you mean you want to show the total averagex in above?

If this , you can try to use this:

Measure = CALCULATE( AVERAGEX(SUMMARIZE('Table','Table'[TestedDay],"CalMeasure",DISTINCTCOUNT('Table'[Resources])),[CalMeasure]) , ALL('Table'[Area]))

The result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[SantoshKumar]

@v-yueyunzh-msft  No, your condition applies for entire set. i have ask for specific for Area3 only. such as 
When i select Area3 , irrespective of selected Region (only for Central or East), it should show the combined and for other selections it should as normal values only.

[v-yueyunzh-msft · Accepted solution]

Hi , @SantoshKumar 

You can try to add some judgement in dax , like this:

Measure = var _special= CALCULATE( AVERAGEX(SUMMARIZE('Table','Table'[TestedDay],"CalMeasure",DISTINCTCOUNT('Table'[Resources])),[CalMeasure]) , ALL('Table'[Area]))
var _normal = AVERAGEX(SUMMARIZE('Table','Table'[TestedDay],"CalMeasure",DISTINCTCOUNT('Table'[Resources])),[CalMeasure])
return
IF(SELECTEDVALUE('Table'[Area])="Area3" && SELECTEDVALUE('Table'[Region]) in {"Central","East"} , _special , _normal)

Then we can only get the special data when we select Area3 , irrespective of selected Region.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly