Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 nameQTYValue
A510
B1010
A12
C515
A12
C1030
A2550
B1010

 

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

 

Table 2: Name: Sales_before_mnth

Product nameQTYValue
A100200
B5050
C3090


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?

 

1 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:
FreemanZ_0-1670062844133.png

 

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

FreemanZ_2-1670062973744.png

 


 

 

View solution in original post

6 REPLIES 6
FreemanZ
Super User
Super User

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

Anonymous
Not applicable

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:
FreemanZ_0-1670062844133.png

 

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

FreemanZ_2-1670062973744.png

 


 

 

Anonymous
Not applicable

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. 

Anonymous
Not applicable

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.  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors