Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Dear Memebrs,
sicne couple of days I'm struggling with PBI on how to calucalte the average of another measure?
Case as follows:
I do have a table with following meaures:
Measure are calculated as follows:
The only thing I want to achieve is somehow re-calcualte avrage on Manager level to show average of all averages OR sum of all distinct counts on OU level divided by sum of all reps - desired view below:
I'm totally vulenrable now - thought it's easy-peasy task but stucked with it for days... Help, please anyone!
Solved! Go to Solution.
Hello,
thank for you help - already managed to find out the solution for my problem - works like a charm now!
# OU* =
COUNTX (
FILTER (
SUMMARIZE (
'DB',
'DB'[Scenario],
'DB'[Sales Rep Number],
'DB'[OU Hierarchy],
"Count OU", COUNT ( 'DB'[OU Hierarchy] ),
"Sales Rep", SUM ( 'DB'[USD] )
),
'DB'[Scenario] = "CY_ACT"
&& [Sales Rep] > 0
),
[Count OU]
)
Hello,
thank for you help - already managed to find out the solution for my problem - works like a charm now!
# OU* =
COUNTX (
FILTER (
SUMMARIZE (
'DB',
'DB'[Scenario],
'DB'[Sales Rep Number],
'DB'[OU Hierarchy],
"Count OU", COUNT ( 'DB'[OU Hierarchy] ),
"Sales Rep", SUM ( 'DB'[USD] )
),
'DB'[Scenario] = "CY_ACT"
&& [Sales Rep] > 0
),
[Count OU]
)
Hi @ponczula
You can refer to the following solution.
Sample data
Create a measure
Measure =
VAR a =
SUMMARIZE (
ALLSELECTED ( 'Table' ),
[Manager],
[Column1],
"Average", AVERAGE ( 'Table'[Column3] )
)
RETURN
IF (
ISINSCOPE ( 'Table'[Column1] ),
CALCULATE ( AVERAGE ( 'Table'[Column3] ) ),
AVERAGEX ( FILTER ( a, [Manager] IN VALUES ( 'Table'[Manager] ) ), [Average] )
)
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You cannot measure a measure. Instead, implement your business logic in a single measure.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
110 | |
109 | |
94 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |