SUM values only for distinct values in another column

[monkeynuts100]

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.

[Anonymous]

Hi @monkeynuts100, in your example below for Unique ID = 1234, what should your average version number be?

[monkeynuts100]

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).

[Anonymous]

Thanks @monkeynuts100. Does the mean you are also expecting the following average values:

• For 1111 expecting 1
• For 3556 expecting 2

[Phil_Seamark]

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.

[monkeynuts100]

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!

[Anonymous]

If I were doing this in DAX, I would write a measure like this:

Average Version = DIVIDE(SUMX(VALUES(Table1[Version]),[Version]),COUNTROWS(Table1))

[monkeynuts100 · Accepted 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.

[Phil_Seamark]

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

[Vvelarde]

@monkeynuts100

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
)