cancel
Showing results for 
Search instead for 
Did you mean: 
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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors