cancel
Showing results for
Did you mean:
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)

1 ACCEPTED SOLUTION
Solution Sage

@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

7 REPLIES 7
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

Frequent Visitor

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

Solution Sage

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

Frequent Visitor

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)

Solution Sage

@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

Frequent Visitor

@quentin_vigne Thank you for your support.

Result is what I expected.

When the created new table will be refreshed?

Solution Sage

@Nilrem

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

- Quentin

Announcements