Forum Discussion
Count items that reach a value threshold
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
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:
6 Replies
the result of a measure depends on its context. how would you present the threshold check measure?
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"
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: