Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have data like this:
Unique ID | Version |
1234 | 1 |
1234 | 1 |
1234 | 2 |
1234 | 2 |
1234 | 3 |
1111 | 2 |
1111 | 2 |
2223 | 3 |
3556 | 2 |
3556 | 2 |
4432 | 1 |
I need to culculate the average version number for each unique ID - but I don't want to include duplicate versions from the same Unique ID in this calculation. So ideally, I want a column added like this:
Unique ID | Version | Ave Calc |
1234 | 1 | 1 |
1234 | 1 | 0 |
1234 | 2 | 2 |
1234 | 2 | 0 |
1234 | 3 | 3 |
1111 | 2 | 2 |
1111 | 2 | 0 |
2223 | 3 | 3 |
3556 | 2 | 2 |
3556 | 2 | 0 |
4432 | 1 | 1 |
I could then use this column to calculate the average correctly.
I have looked at other posts that talk about DISTINCTCOUNT but this issue seems slightly different and I can't figure out how to get a column like in the above example.
Solved! Go to Solution.
Thanks everyone for your helpful suggestions. In the end, I did some work in my SP to add a new column for my average calc like this:
Unique ID | Version | Ave Calc |
1234 | 1 | 1 |
1234 | 1 | NULL |
1234 | 1 | NULL |
1234 | 1 | NULL |
1234 | 2 | 2 |
1234 | 2 | NULL |
1234 | 3 | 3 |
1111 | 2 | 2 |
1111 | 2 | NULL |
2223 | 3 | 3 |
3556 | 2 | 2 |
3556 | 2 | NULL |
3556 | 2 | NULL |
4432 | 1 | 1 |
The NULLS are needed rather than zeros otherwise the average is affected by the extra rows.
It would be amazing if in future versions of PowerBI it was possible to perform calculations on values used in the visualisations - although I appreciate this will have possible performance implications if missused.
Hi @monkeynuts100, in your example below for Unique ID = 1234, what should your average version number be?
Hey @Vvelarde - thanks for your suggestion. I didn't mention that this is a DirectQuery so it's complaining when I'm trying to use the nested CALCULATE.
@Anonymous - For ID = 1234, I am wanting an average of 1.2 (but without solution I am getting 1.8).
Thanks @monkeynuts100. Does the mean you are also expecting the following average values:
What is the data source you are using for your Direct Query? If it's a SQL database you can probably do this in your select statement.
Well yes - this is true! I guess I was wondering how I could get what I need without having to go back to the SP....
Any pointers on acheiving the same thing in SQL would be welcome!
If I were doing this in DAX, I would write a measure like this:
Average Version = DIVIDE(SUMX(VALUES(Table1[Version]),[Version]),COUNTROWS(Table1))
Thanks everyone for your helpful suggestions. In the end, I did some work in my SP to add a new column for my average calc like this:
Unique ID | Version | Ave Calc |
1234 | 1 | 1 |
1234 | 1 | NULL |
1234 | 1 | NULL |
1234 | 1 | NULL |
1234 | 2 | 2 |
1234 | 2 | NULL |
1234 | 3 | 3 |
1111 | 2 | 2 |
1111 | 2 | NULL |
2223 | 3 | 3 |
3556 | 2 | 2 |
3556 | 2 | NULL |
3556 | 2 | NULL |
4432 | 1 | 1 |
The NULLS are needed rather than zeros otherwise the average is affected by the extra rows.
It would be amazing if in future versions of PowerBI it was possible to perform calculations on values used in the visualisations - although I appreciate this will have possible performance implications if missused.
Here is an SQL based approach to give you ideas how you can update your SP
CREATE TABLE #X ( [UniqueID] Varchar(4) , [Version] Integer ) INSERT #x SELECT '1234' , 1 INSERT #x SELECT '1234' , 1 INSERT #x SELECT '1234' , 2 INSERT #x SELECT '1234' , 2 INSERT #x SELECT '1234' , 3 INSERT #x SELECT '1111' , 2 INSERT #x SELECT '1111' , 2 INSERT #x SELECT '2223' , 3 INSERT #x SELECT '3356' , 3 INSERT #x SELECT '3356' , 3 INSERT #x SELECT '4432' , 1 SELECT IDENTITY(int,1,1) AS OrderID , * INTO #Y FROM #X ORDER BY [UniqueID] , [Version] DELETE #y FROM #y INNER JOIN (SELECT MIN(OrderID) AS MinOrderID , UniqueID , [Version] FROM #Y GROUP BY UniqueID , [Version] HAVING COUNT(*) > 1) AS D ON D.UniqueID = #y.UniqueID AND #y.Version= D.Version AND #y.OrderID != D.MinOrderID SELECT [UniqueID] , [Version] FROM #y
If you want a Calculated Column with this data, my solution is:
In Edit Query add a Index Column.
After that create the calculated column:
VersionRev = IF ( Table1[Index] = CALCULATE ( MIN ( Table1[Index] ), ALLEXCEPT ( Table1, Table1[Unique ID], Table1[Version] ) ), Table1[Version], 0 )
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
74 | |
57 | |
38 | |
33 |
User | Count |
---|---|
71 | |
66 | |
57 | |
49 | |
47 |