Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

use 2 lines on visual that reponse on diff filter

I’d like to create a formula to make a benchmark so I can have 2 lines in the graphs on the left below, one line that will change based on the office selected in the office filter (bottom left) and the other line that will be the average of all offices in the country of the selected office (so if OfficeA is selected in the filter, the average line will show the average of USA, if OfficeB is selected the average line will be Canada)

Right now I use this formula:

All Avarage = CALCUALTE ((OfficeAVG), ALL(Fact_numbers[Office]), FILTER(Fact_Numbers[Country]="USA"))
This will show the Avarage of the office of all centers in USA but this is more passive the active.. So

 
 
Need Help
Tips and Tricks

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try a measure like

    measure =
    var _cnt = allselected(Fact_numbers[country]) //filter on same table should give country
    return
    CALCUALTE ((OfficeAVG), FILTER(ALL(Fact_numbers), Fact_Numbers[Country]=_cnt))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      So, that means that this will be the Office avg of the selected Office..

       

      1. So my first line (in line chart) must be the AVG of the selected Office

      2. The other line is the AVG of all office in the country that the office is selected (without the selected office) (benchmark)

       

      This calcualtion that you type:

      Dont look at country  and return the avg of the selected centrer in the country

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        Anonymous , Once I select an office, I should able to gets its avg by simple avg.

        As that is in the same table allselected should give me country and they filter country using all on the table , means only that country filter.

         

        or

        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Error in the measure calculation. One table of several value has been given when it should be only one value

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  Anonymous  ,

    According to your description, I create this data:

    Here are the steps you can follow：

    1. Create calculated column.

    Qt = QUARTER('Table'[date])
    Year = YEAR('Table'[date])

    2. Create measure.

    office_select =
var _select=SELECTEDVALUE('Table'[office])
var _avg=
CALCULATE(AVERAGE('Table'[amount]),FILTER(ALL('Table'),'Table'[office]=_select&&'Table'[Year]=MAX('Table'[Year])&&'Table'[Qt]=MAX('Table'[Qt])))
return _avg
    region_select =
var _select=SELECTEDVALUE('Table'[region])
var _avg=
CALCULATE(AVERAGE('Table'[amount]),FILTER('Table','Table'[Year]=MAX('Table'[Year])&&'Table'[Qt]=MAX('Table'[Qt])&&'Table'[region]=_select))
return _avg

    3. Result.

    Select office Slice will display the average number of offices

     

    Selecting a region will display the average number of regions

     

     

     

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

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