Forum Discussion
Count items that reach a value threshold
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:
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"
Super Useraha, then you need a dimtable and a measure with this:
try to plot a Table Visual with the measure and the Product Column from the dimtable. I tried and it worked like this:
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.
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.