Using iterators where there are duplicate values

[dicken]

Hi,
I've been doing a bit of experimenting, so I have a simple measure ; SUM(Tsales [Units] )

Whichi I have then wrapped in a AVERAGEX and SUMX , its only a two column table Item and unit and I made sure I had duplicate values,
which as it's tiny it is easy to see how the duplicates are grouped up ; so i have 3 a's a and 2 , a and 2 , a and 6
which seems to return ( 2 + 2 ) + 2 + 2 ) + 6. My question is this if I'm using anything enduing in X around a measure make sure you have a unique identifier
, any advice or reading suggestions welcome.

Richard

 

[GraH]

Not sure I understand what you explain.

The interation happens on row level. That's simply it.
Say you quantity and price in a sales table, you can use averagex(table sales, [Quantity]*[Price])
It will give you the average of Total sales, which you need to calculates first on the row level.
So I do not really understand why you say it is sort of grouping duplicates values. It is not. That's the filter context of the coordinated fields. At least that's my assumption on the thing you describe.

Reading sugestion: any book by @Matt Allington ( member here), Avi Singh, Rob Collie. The books + website of 'the Italians', Rob's place,...

Home - SQLBI

Business Intelligence with passion

www.sqlbi.com

P3 Adaptive | Power BI Consulting & MS Data Platform

P3 Adaptive is a premier Microsoft Data Platform consultant firm. Let our experts put your business-critical data to work and solve your data problems today!

p3adaptive.com

PS: I should follow my own advice as I tend to forget to move on with DAX.

 

[dicken]

Not sure I understand what you explain.

The interation happens on row level. That's simply it.
Say you quantity and price in a sales table, you can use averagex(table sales, [Quantity]*[Price])
It will give you the average of Total sales, which you need to calculates first on the row level.
So I do not really understand why you say it is sort of grouping duplicates values. It is not. That's the filter context of the coordinated fields. At least that's my assumption on the thing you describe.

Reading sugestion: any book by @Matt Allington ( member here), Avi Singh, Rob Collie. The books + website of 'the Italians', Rob's place,...

Home - SQLBI

Business Intelligence with passion

www.sqlbi.com

P3 Adaptive | Power BI Consulting & MS Data Platform

P3 Adaptive is a premier Microsoft Data Platform consultant firm. Let our experts put your business-critical data to work and solve your data problems today!

p3adaptive.com

PS: I should follow my own advice as I tend to forget to move on with DAX.

Thanks I'll look at the sites, I do have Matts book and another by a couple of Italians, also I've found Mike Girvin covers the problem in MSPTDA 18 where he points out that the problem won't occur if you use a formula rather than a measure and it's also ( just read this morning which doing the washing) in the Definitive Guide chapters 5 and 7 .

Richard

 

[dicken]

Not sure I understand what you explain.

The interation happens on row level. That's simply it.
Say you quantity and price in a sales table, you can use averagex(table sales, [Quantity]*[Price])
It will give you the average of Total sales, which you need to calculates first on the row level.
So I do not really understand why you say it is sort of grouping duplicates values. It is not. That's the filter context of the coordinated fields. At least that's my assumption on the thing you describe.

Reading sugestion: any book by @Matt Allington ( member here), Avi Singh, Rob Collie. The books + website of 'the Italians', Rob's place,...

Home - SQLBI

Business Intelligence with passion

www.sqlbi.com

P3 Adaptive | Power BI Consulting & MS Data Platform

P3 Adaptive is a premier Microsoft Data Platform consultant firm. Let our experts put your business-critical data to work and solve your data problems today!

p3adaptive.com

PS: I should follow my own advice as I tend to forget to move on with DAX.

Just to add as you said you didn't quite get what the problem was;
it's this with and iterator every time it comes across duplicate values it groups them together,
so a 1
a 2
a 2 , so =SUMX ( Table , SUM(sales) ) would give (1 + ( 2 + 2 ) + (2 + 2 ) ) , I know this isn't realistic but illustrates the point.
Richard.