cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
venkatnitt
New Member

Average should remain same when particular set of slicers Selected

We have are requirement where the average value  in the chart should remain same irresepective of when particular Slicer is selected . Eg

 

Slicer on : Division , Region 

 

DivisionRegionValue
USALA100
USASFO200
USAWES300
USAEAS150
MEXPR57
MEXCR100
  Average 151.17

 

 

The Average is All regions is 151.17

 

When i apply filter (slicer ) on Region ,   the Average value should not change . It should remain 151.  

 

DivisionRegionValueAverage
USALA100151.17

 

Used CACULATE -AVERAGE-FILTER-ALLEXCEPT , still it filters when i selected particular region and it average changes .

 

 

But When i apply filter (slicer) on the Division , the Average should Change. 

 

DivisionRegionValueAverage
USALA100750
USASFO200750
USAWES300750
USAEAS150750

 

Is there any way to do this?

 

 

3 REPLIES 3
Super User IV
Super User IV

Re: Average should remain same when particular set of slicers Selected

Make sure your measure is:

 

Measure = CALCULATE(AVERAGE([Value]),ALLEXCEPT([Division]))

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Microsoft
Microsoft

Re: Average should remain same when particular set of slicers Selected

Hi @venkatnitt,

 

Could you try the formula below to see if it works in your scenario? Smiley Happy

 

Avg = 
CALCULATE (
    AVERAGE ( Table1[Value] ),
    ALLSELECTED ( Table1[Division] ),
    ALLEXCEPT ( Table1, Table1[Division] )
)

 

Regards

Microsoft
Microsoft

Re: Average should remain same when particular set of slicers Selected

Hi @venkatnitt,

 

Have you tried the solution provided above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

 

If you still have any question on this issue, feel free to post here. Smiley Happy

 

Regards

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors