Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello - Trying to develop a measure excluding certain data.
Project | PF | Avg PF | Exclude? |
Project 1 | 2.47 | 2.20 | 1 |
Project 2 | 1.54 | 2.20 | |
Project 3 | 2.59 | 2.20 |
Currently Average is calculated correctly [2.47 + 1.54 + 2.59] / 3 = 2.20
But i wanted to ignore Project 1 in the average calculation (as exclude = 1). Hence i want the average PF to be [1.54 +2.59] = 2.07
I am currently using the below formula, but it is still considering all 3 projects
Avg PF =
I am trying to show a CHART (Column-Line) visual, and hence i need to show all the 3 x Projects PF values in columns, but the average line should be based on 2 x Projects.
Kindly note "Exclude" is a measure based on=
Appreciate the support
Solved! Go to Solution.
Hi,
Like this?
___AvgforNonExcluded = CALCULATE(
AVERAGE('Table'[PF]), FILTER(ALL('Table'),'Table'[Exclude]=BLANK()))
As seen here:
Link to file.
Please mark as solution if so. Thumbs up for the effort are appreciated.
Kind regards,
Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
@hackfifi
I forgot to remove the variable, as it always stays static, please check now:
Avg PF (Excluded) =
AVERAGEX(
FILTER(ALL(Table2[Project]),[Exclude] <> 1),
[PF]
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi,
Like this?
___AvgforNonExcluded = CALCULATE(
AVERAGE('Table'[PF]), FILTER(ALL('Table'),'Table'[Exclude]=BLANK()))
As seen here:
Link to file.
Please mark as solution if so. Thumbs up for the effort are appreciated.
Kind regards,
Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi,
Does this measure work? Drag this measure to a card visual.
=averagex(filter(values(data[project]),data[exclude?]<>1),[pf])
Hope this helps.
Will this formula helps you!? Please Share your Kudoes
Proud to be a Super User!
@VijayP - can you copy / paste the code please?
Also FYI -
"PF" is a measure
i have a filter for selection of projects i.e. Projects_Alias
and also a filter for selection of refrence projects i.e. 'Ref Project'. The list of projects is the same of both filters
But when a project is selected from 'Ref Project', it calculates a "1" for the "exclude measure"
@hackfifi
create PF as a column and use this measure to get the result
IF you want use PF As measure Change 'Table'[PF] to [PF] and remaining formula will be same
AVG PF2 =
VAR totalpf = CALCULATE(SUM('Table'[PF]),All('Table'))
VAR prjct1pf = CALCULATE(SUM('Table'[PF]),
FILTER(ALL('Table'),'Table'[Project]=SELECTEDVALUE('Table'[Project])))
VAR balance = totalpf-prjct1pf
VAR cnt = CALCULATE(COUNT('Table'[Project]),All('Table'))
VAR selectcnt = CALCULATE(COUNT('Table'[Proejct]),
FILTER(ALL('Table'),'Table'[Project]=SELECTEDVALUE('Table'[Project])))
VAR cntbalance = cnt-selectcnt
RETURN
Divide(balance,cntbalance,0)
Proud to be a Super User!
Sorry @VijayP it did not work...i was getting AVGPF2 value is 0
i have to use "ALLSELECTED" as i have over 100 projects in the dataset, but i have selected only 3
I am essentially trying to calculate the average PF of SELECTED PROJECTS where EXCLUDE<>1
@hackfifi
You may try this measure:
Avg PF (Excluded) =
VAR __Exclude = [Exclude] RETURN
AVERAGEX(
FILTER( ALLSELECTED(Table2[Project], Table2[PF]) , __Exclude <> 1),
Table2[PF]
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
hi @Fowmy ...kindly note PF is a measure, so i modified your formula as below
@hackfifi
Then use it this way, if it doesn't work, show the expected results based on your sample.
Avg PF (Excluded) =
VAR __Exclude = [Exclude] RETURN
AVERAGEX(
FILTER( ALLSELECTED(Table2), __Exclude <> 1),
[PF]
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy - Based on the sample, the expected result should be the average of the exclude <> 1 i.e. (1.54 + 2.59) / 2 = 2.07
Currently the result of your formula is still the average of the 3 data points i.e. (2.47+1.54+2.59)/3 which is 2.20
Hope that is clear, and thanks again for your time.
@hackfifi
I forgot to remove the variable, as it always stays static, please check now:
Avg PF (Excluded) =
AVERAGEX(
FILTER(ALL(Table2[Project]),[Exclude] <> 1),
[PF]
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks @Fowmy - that worked.
So just for my understanding, how did it change by just removing the variable? i thought the variable was doing the same thing?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |