Number of plant under KPI target

[Nilrem]

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.

[quentin_vigne]

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

[Nilrem]

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

[quentin_vigne]

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

[Nilrem]

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)

[quentin_vigne · 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

[Nilrem]

@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.

[quentin_vigne]

@Nilrem

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

- Quentin