Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
aha, 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:
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:
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?
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.
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |