Calculate number of customers with condition

[Carlaf]

Good morning,

i've got a model with two tables.

Tabel 1 contains:

customer, data, sales, budget

and Table 2 contains:

customer, old credit's value,

-->i woul like to calculate the number of customers' that have a negative difference between budget and old credit's value.

How can i do it?

Thanks

C

[v-yadongf-msft · Accepted solution]

Hi @Carlaf ,

Please create a new column in table2:

New_Budget = IF('Table2'[Year] = 2021,LOOKUPVALUE('Table1'[Budget],'Table1'[Customer],'Table2'[Customer]))

Do you just want to count the numbers of 2021?

Count = SUMX('Table2',IF('Table2'[New_Budget] < 'Table2'[Old credit's value] && 'Table2'[New_Budget] <> BLANK(), 1 , 0 ))

If I misunderstand your demands, please feel free to contact us in time.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Carlaf]

I couldn't add the pbix format.

[Shaurya]

Hi @Carlaf,

I would first pull the old credit value in the first table by using LOOKUPVALUE to create a new column.

Old Credit Value = LOOKUPVALUE('Table 2'[Old Credit Value],'Table 2'[Customer],'Table 1'[Customer])

Then count rows by using:

Count = SUMX('Table 1',IF('Table 1'[Budget]<'Table 1'[Old Credit Value],1,0)

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Graphical Comparison 

[Carlaf]

Hi,

thanks for your answer.

For the first step i've got this error

Maybe because i also have a data field in the second table... and it gives more data

[msusa_pbi]

Hi,

Can you attach the sample data not in image format?