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
Nilrem
Frequent Visitor

Number of plant under KPI target

Dear All,

 

Example data:

 

Plant                                      Order                    Weight usage

A                                             1                              0,7

B                                             2                              0,6

C                                             3                              0,7

B                                             4                              0,85

B                                             5                              0,88

C                                             6                              0,9

A                                             7                              0,7

A                                             8                              0,5

B                                             9                              0,9

 

 

 

Mesaurs:

- Weight usage average: Weight_AVG_PCT

- Numeber of the distinct order: Order_Count

- Number of distinct Plant: Plant_count

 

How can I calculate the number of plant where Weight_AVG_PCT is less then 0,85

 

Following dax formula doesn't work

Plant_count_under target = Calculate(Plant_count; weight_avg_pct<0,85)

 

Thank you in advance for your hint.

1 ACCEPTED SOLUTION

@Nilrem

 

Sorry for misunderstanding.

 

Here a solution that fit your needs

 

1 - Go to modelisation tab

2 - Create new table with following formula :

Solution = SUMMARIZE(Table1;Table1[Plant];"Average";AVERAGE(Table1[Weight Usage]))

3 - Then add the formula that count the number of avg under your kpi

Testing = IF('Table'[Average]<0,85;1;0)

 

 

- Quentin

View solution in original post

7 REPLIES 7
quentin_vigne
Solution Sage
Solution Sage

Hi @Nilrem

 

You should try this : 

 

Column = CALCULATE(COUNT(Plant);Weight_AVG_PCT<0.85)

Also depending on PBI version the ; could be a , on the calculate formula

 

- Quentin

Unfortunately also doesn't work. BI version 2.54 64 bit

Hi @Nilrem

 

i've done this and it should work

 

Column = IF(CALCULATE([Weight_AVG_PCT];DISTINCT(Table1[Plant]))<0,85;1;0)

Then you use this column in whatever you want, and you sum it to have the number of plants < 0.85

Here my result display 5  

 

- Quentin

Hi,

 

Thank you for your reply. Maybe my problem is not written down well. I try to exaplain with example spreadsheet:

 

Plant                                      Order                    Weight usage

A                                             1                              0,7

B                                             2                              0,6

C                                             3                              0,7

B                                             4                              0,85

B                                             5                              0,88

C                                             6                              0,9

A                                             7                              0,7

A                                             8                              0,5

B                                             9                              0,9

 

(your result is 5, it is nothing else just count the order number below 0,85 Weight usage)

For me the following need: 

Weight average py plant: 

A (0,7+0,7+0,5)/3 = 0,56

B (0,6+0,85+0,88+0,9)/4 =0,8075

C (0,7 + 0,9)/2 = 0,8

 

Result has to be 3 (All three plant below the target)

 

@Nilrem

 

Sorry for misunderstanding.

 

Here a solution that fit your needs

 

1 - Go to modelisation tab

2 - Create new table with following formula :

Solution = SUMMARIZE(Table1;Table1[Plant];"Average";AVERAGE(Table1[Weight Usage]))

3 - Then add the formula that count the number of avg under your kpi

Testing = IF('Table'[Average]<0,85;1;0)

 

 

- Quentin

@quentin_vigne Thank you for your support.

 

Result is what I expected.

 

 When the created new table will be refreshed?

 

Thank you for your support.

 

@Nilrem

 

If everythings works fine, your table will be refreshed at the same time as your data are

 

- Quentin

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.