Forum Discussion

kulpowerbi's avatar
kulpowerbi
Helper I
2 years ago
Solved

calculate dynamic average measure

I have this data

categorycountrymetric
AUS20
ACanada10
ABoth30
i want to use country column as slicer visual but, I don't wanna show the value 'Both' in the slicer. I will filter out this particular value from the slicer. so its not visible to end user. but it should not impact the mesure. I wll put the mesure in a Card visual.
 
Now a mesure need to write, whcih calculate average based on this condition. 
When selected US, the average should be 25. it's calculated based on  (20+30)/2 = 25 and
When selected Canada, the average should be 20. it's calculated based on  (10+30)/2 = 20 and when selected 
US and Canda in slicer then the average should be 20 (10+20+30)/3 = 20
 
i have written this mesure but it's not showing correct value.
image refrence.. and here is the file link Slicer.pbix

 

 
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi kulpowerbi ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create a slicer table.

    Table 2 = EXCEPT(VALUES('Table'[country]),{"Both"})

    (3) We can create a measure. 

    average = 
var _a=CALCULATE(SUM('Table'[metric]),'Table'[country]= "Both" )
var _b=COUNTROWS(ALLSELECTED('Table 2'))
var _c=IF(ISFILTERED('Table 2'[country]),1,0)
var _d=CALCULATE(SUM('Table'[metric]),FILTER(ALL('Table'),'Table'[country] in VALUES('Table 2'[country])))
RETURN DIVIDE(_d+_a,_b+_c)

    (4) Then the result is as follows.

    Best Regards,

    Neeko Tang

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

2 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    kulpowerbi 

    hello 

     

    try this code : 
    measure =  

    var  c =  calculate(max(table_name[metric]) ,  country =  Both ) 

     

    var t =  if( isfiltered( table_country[country_column])  , 1 , 0 ) 

     

    var res = 

    ( sum(metric) + c ) /  countrows(table_name) + t 

     

    return res

     

     

    let me know if it works for you  .

     

     

     

    If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi kulpowerbi ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create a slicer table.

    Table 2 = EXCEPT(VALUES('Table'[country]),{"Both"})

    (3) We can create a measure. 

    average = 
var _a=CALCULATE(SUM('Table'[metric]),'Table'[country]= "Both" )
var _b=COUNTROWS(ALLSELECTED('Table 2'))
var _c=IF(ISFILTERED('Table 2'[country]),1,0)
var _d=CALCULATE(SUM('Table'[metric]),FILTER(ALL('Table'),'Table'[country] in VALUES('Table 2'[country])))
RETURN DIVIDE(_d+_a,_b+_c)

    (4) Then the result is as follows.

    Best Regards,

    Neeko Tang

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