cancel
Showing results for
Did you mean:
Regular Visitor

## SUM values only for distinct values in another column

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.

1 ACCEPTED SOLUTION
Regular Visitor

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.

9 REPLIES 9
Anonymous
Not applicable

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

Regular Visitor

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
Not applicable

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

• For 1111 expecting 1
• For 3556 expecting 2
Microsoft

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.

Proud to be a Datanaut!

Regular Visitor

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
Not applicable

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

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

Regular Visitor

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.

Microsoft

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```

Proud to be a Datanaut!

Community Champion

@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
)```

Lima - Peru