Count items that reach a value threshold

[Anonymous]

Hi, following my previous discussion here, I need to calculate a measure, that gives a number of products that reach a certain threshold.

For example I have 2 tables

Table1: Sales_current_mnth 

Product name	QTY	Value
A	5	10
B	10	10
A	1	2
C	5	15
A	1	2
C	10	30
A	25	50
B	10	10

Additionally, I have an aggregated list of sales from previous months:

Table 2: Name: Sales_before_mnth

Product name	QTY	Value
A	100	200
B	50	50
C	30	90

We can caclulate the Total value as 

Total Sales = SUM ( Sales_before_mnth[Value] ) + SUM ( Sales_current_mnth[Value] )

How can I construct a measure that shows, That e.g. Product A has reached a planned threshold of 250?

[FreemanZ]

the result of a measure depends on its context. how would you present the threshold check measure?

[Anonymous]

HI, I need to calculate a SUM of Value per product, add a value from previous month. The calculate value has to be compared with a threshold. 

For example:

For product A

Sum of Value (current mnth) = 10 + 2 + 2 + 50 = 64

Total value = 64 + 200 = 264

264 > 250 >>> measure returns a string "Plan exceeded"

[FreemanZ · Accepted solution]

aha, then you need a dimtable and a measure with this:

PlanCheck =

VAR TtlValue =

SUMX(CurrentMonth, CurrentMonth[Value])

+

SUMX('PreviousMonth', 'PreviousMonth'[Value])

RETURN IF(TtlValue>=250, "Plan exceeded", "Plan OK")

model:

try to plot a Table Visual with the measure and the Product Column from the dimtable. I tried and it worked like this:

[Anonymous]

Hi, Thank you for the answer! Why I do need a dimtable here, can I simply create a direct relationship between CurrentMnth and PrevMnth?

Also in a real situtaion I have about 100k products, how I can collect them in one dim table?

[FreemanZ]

yes, if your prevmonth table is qualified as a dimtable: the products are unique and covers all the products in currentmonth. if so, you can still use the same code for a measure and plot with the product column from the prevmonth table. 

[Anonymous]

Hi, Thank you very much for your clarification! I have the last qustion for this problem yet: how can I create a measure that counts a number of products that ecxeed a threshold. 

E.g. in the data above I have only 1 such product (item A) and the measure should return 1 in this case.