Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KB_BI_GG
Regular Visitor

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)

Line.PNG

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

 
 
6 REPLIES 6
v-yangliu-msft
Community Support
Community Support

Hi  @KB_BI_GG  ,

According to your description, I create this data:

v-yangliu-msft_0-1614817855696.png

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

v-yangliu-msft_1-1614817855713.jpeg

 

Selecting a region will display the average number of regions

v-yangliu-msft_2-1614817855717.jpeg

 

 

 

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.

KB_BI_GG
Regular Visitor

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

amitchandak
Super User
Super User

@KB_BI_GG , 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))

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

@KB_BI_GG , 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.

Well there is 2 filters,

1. Office

2. Country   

 

Most of the time the person will select the office because he want to know the numbers of the office, but there must be a second line that will show the avg of all offices (so officeA is on USA so that means all the office without the selected officeA (so all USA offices minus OfficeA)...

Maybe is good start to get all and then find out how the get only the office and not the selected one to get a overall avarage.

 

Sorry about the filtering part..

 

Right know not able to get the sample data.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.