count number of records based on conditions and multiplication factor

[Anonymous]

Hi there,

I'm finding myself stuck on the following:

I need to count the number of records in my table depending on the content of a "type" column, I need to multiply

the number of records * a factor or not. for example:

if I were to use a distinctcount, I would end up with 3 in this example. What I'm after is to end up with 3.3 (1+1.3+1).  I can't seem to figure out which formula to apply. 

suggestions welcome! thanks

[d_gosbell]

You can do row by row operations using one of the "X" family of measures (eg. SUMX, COUNTX, AVERAGEX, etc)

So you could do SUMX( Table1, Table1[Factor] * Table1[Count] )

Or if the factor is just there for illustration purposes you could do the following

SUMX( Table1, IF( Table1[type] = "b", 1.3, 1) )

[Anonymous]

thank you @d_gosbell . I understand the logic. What I don't have is the factor itself, but a value which implies the factor 1 or 1.3. So, I would need to do a count[123] and either multiply this count by 1 or by 1.3 depending on the value, which implies the multiplying factor.  or would you suggest to add the factor to the table in power query rather. 

[d_gosbell]

---

@Anonymous wrote:

What I don't have is the factor itself, but a value which implies the factor 1 or 1.3. 

---

So the second expression I suggested should achieve this it just returns a 1 or 1.3 depending on the value of [type] and then sums them (which should be the same as count and multiply)

SUMX( Table1, IF( Table1[type] = "b", 1.3, 1) )

Or you could get more literal and do the following

SUMX( VALUES( Table1[type] ) , [Count] * IF( VALUES(Table1[type] ) = "b", 1.3, 1 ) )

But you have an extra context transition by referencing the count measure so it will be slightly less performant