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
I have a table, lets says it has the following columns/data
Group1 | Group2 | Group3 | Percent |
1 | A | i | 5 |
1 | B | i | 10 |
1 | C | i | 15 |
2 | A | i | 20 |
2 | B | i | 25 |
2 | C | i | 30 |
3 | A | i | 40 |
3 | B | i | 45 |
3 | C | i | 50 |
1 | A | ii | 15 |
1 | B | ii | 30 |
1 | C | ii | 60 |
2 | A | ii | 20 |
2 | B | ii | 35 |
2 | C | ii | 65 |
3 | A | ii | 30 |
3 | B | ii | 45 |
3 | C | ii | 75 |
I want to solve for the average of Group 3 by Group 2, so the result would look something like this (if presented as a table):
Group2 | Group3 | Avg PCT |
A | i | 21.666 |
A | ii | 21.666 |
B | i | 26.666 |
B | ii | 36.666 |
C | i | 31.666 |
C | ii | 66.666 |
Finally I want to multiply the AVG result by a [Measure] (seems like the easy part). I want to do this as a measure so that it is fully dynamic, ie, while my example here summarizes to Group2, you could just as likely calculate the overall average of Group3.
I've taken multiple approachs. I feel like it shouldn't be this hard, as usual I'm likely overlooking something basic with context. I thought I'd be able to use a CALCULATETABLE to create a virtual table, then AVERAGEX the result, but the numbers don't add up.
Thanks,
-R
// Actually, it's very easy with minimal coding.
// All you have to do is have one measure:
[Average] = AVERAGE( T[Percent] )
// and the rest is done through slicing
// on dimension attributes in your visuals.
// If you want to have a measure that
// multiplies this and the other one...
[Multiplication] = [Average] * [Measure]
// Each measure is dynamic in the sense
// that it responds to different contexts
// which you set by slicing and dicing
// on your dimensions.
Best
D
Both measures return the same result, that is good.
The bad, the result is still wrong, and for the life of me I can't figure out why. Since there is no "CALCULATE" everything should be in the same context, and yet its not adding up. See screenshot below.
First table represents the data table, you can see each unique row.
Second table shows the rows being rolled up, the math is correct on the Highlighted Column showing that it is summing correctly; 45.74 = SUM(3x rows). The measure which is calculating average is wrong in some cases, correct in others and I don't know why. Again, everything should be in context here, so its not like there is a row "hiding" somewhere that would be influcencing the calculation. If I set the AVG % column to "Average" for its summarization, I get the same incorrect result, so clearly there is something I'm missing in my data/model, I just don't know what... 😞
Minutes after typing my last response....
There is an additional level of categorization that I want to ignore. So what is the best way to roll it up to ignore that additional level of categorization and average based on the categorization level I do care about? I guess this is why I originally went down the road of a CalculatedTable, but that was still returning the same Average Result, so clearly I was doing something wrong there.
Hello all, A family trip and a few other deadlines required me to put this to the side for a few weeks. But I'm back at it.
Please find a sample PBI file on my OneDrive.
Here is what the file looks like. The goal is that the measure currently named [Measure] (see table in lower left) would be effectively divided by the Count of BenchmarkName (illustrated in the table in the upper left). Right now it divides by the count of rows at the data's most granular level (as illustrated by the table in the lower right). The custom colums could potentially be further simplified, hence there are two right now that have the same result.
As to averaging percentages, I did research the concerns raised, but I think in this case I'm "ok" because of the nature of the percentages and what they represent. Regardless in my mind that is a minor issue as compared to arriving at the final result I'm looking for. "Fixing" how I arrive at the % value shouldn't make a big difference compared to resolving my current impasse.
Thank you!
Hi @Anonymous ,
I have just dragged and dropped the columns from the fields to the visualization view.
The filter context of Power BI does the work for me.
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
Hi @Anonymous ,
And incase you want a measure
Percent Measure =
AVERAGEX (
FILTER (
'Table',
'Table'[Group2]
= MAX ( 'Table'[Group2] )
&& 'Table'[Group3]
= MAX ( 'Table'[Group3] )
),
'Table'[Percent]
)
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
@Anonymous , Maybe I overlooked. But I miss logic you have Average. Can you explain with example
The average is calculated as
For each unique Group2, average the Percent by each unique Group3. Hence the results shown in the second sample table where the column "AvgPCT" is the outcome of the calculations.
Hope this helps.
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 |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
58 | |
19 | |
12 | |
11 | |
10 |