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.
So there's the SUM function as a measure or explicit measure.
A, 10
A, 20
B, 15
Returns this:
A, 30
B, 15
Now let's say a faulty table already summed the values, but the amount of rows is equal to the amount of rows before the sum. Like this:
A, 30
A, 30
B, 15
How can I create the opposite measure of SUM that divides the value by the count it occurs per group (A/B in this example), resulting in this:
A, 15
A, 15
B, 15
As you can see the end result is more like a prediction than true to reality, but that's okay.
Thanks!
Hi @richard-powerbi ,
Count the number of rows for each id and divide it by the total.
Create a Calculated Column
Regards,
Harsh Nathani
Here is an expressiont that works with your sample data. I called the table 'Reverse'.
Reverse Sum =
VAR __thisletter =
MIN ( Reverse[Letter] )
VAR __count =
CALCULATE (
COUNTROWS ( Reverse ),
ALL ( Reverse ),
Reverse[Letter] = __thisletter
)
VAR __thisaverage =
AVERAGE ( Reverse[Number] )
RETURN
DIVIDE ( __thisaverage, __count )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
This post should help you get there;
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!
Don't use DAX to fix a mistake that was made in a previous step. Prevent that mistake in your process.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |