Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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)
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.
If everythings works fine, your table will be refreshed at the same time as your data are
- Quentin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
101 | |
76 | |
64 | |
63 |
User | Count |
---|---|
141 | |
105 | |
101 | |
80 | |
67 |