Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Hi @KB_BI_GG ,
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.
Error in the measure calculation. One table of several value has been given when it should be only one value
@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.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |