cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
monkeynuts100
Regular Visitor

SUM values only for distinct values in another column

I have data like this:

 

Unique IDVersion
12341
12341
12342
12342
12343
11112
11112
22233
35562
35562
44321


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 IDVersionAve Calc
123411
123410
123422
123420
123433
111122
111120
222333
355622
355620
443211

 

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

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 IDVersionAve Calc
123411
12341NULL
12341NULL
12341NULL
123422
12342NULL
123433
111122
11112NULL
222333
355622
35562NULL
35562NULL
443211

 

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.

View solution in original post

9 REPLIES 9
JaredK
Kudo Collector
Kudo Collector

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

Jared Knutzen | Visualization Consultant
Decisive Data | www.decisivedata.net

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.

 

@JaredK - 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:

 

  • For 1111 expecting 1
  • For 3556 expecting 2
Jared Knutzen | Visualization Consultant
Decisive Data | www.decisivedata.net

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

Jared Knutzen | Visualization Consultant
Decisive Data | www.decisivedata.net

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 IDVersionAve Calc
123411
12341NULL
12341NULL
12341NULL
123422
12342NULL
123433
111122
11112NULL
222333
355622
35562NULL
35562NULL
443211

 

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.

View solution in original post

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Vvelarde
Community Champion
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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.