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.
Need to calculate sum of BinCount based on a condition:
I have the following data in raw form.
Category | Device | Lot | Screen | BinName | BinFlag | BinCount |
Cat1 | Dev1 | L1 | 0 | 1 | P | 500 |
Cat1 | Dev1 | L1 | 0 | 2 | F | 300 |
Cat1 | Dev1 | L1 | 0 | 3 | F | 200 |
Cat1 | Dev1 | L1 | 1 | 1 | P | 200 |
Cat1 | Dev1 | L1 | 1 | 2 | F | 150 |
Cat1 | Dev1 | L1 | 1 | 3 | F | 100 |
Cat1 | Dev1 | L1 | 1 | 4 | F | 50 |
Cat1 | Dev1 | L1 | 2 | 1 | P | 200 |
Cat1 | Dev1 | L1 | 2 | 5 | F | 100 |
Cat1 | Dev1 | L2 | 0 | 1 | P | 600 |
Cat1 | Dev1 | L2 | 0 | 3 | F | 100 |
Cat1 | Dev1 | L2 | 0 | 4 | F | 100 |
Cat1 | Dev1 | L2 | 0 | 9 | F | 50 |
Cat1 | Dev1 | L2 | 1 | 1 | P | 150 |
Cat1 | Dev1 | L2 | 1 | 3 | F | 60 |
Cat1 | Dev1 | L2 | 1 | 4 | F | 40 |
It can also be represented as following:
BinName | ||||||||||
Lot | Screen | Total | P | F | 1 | 2 | 3 | 4 | 5 | 9 |
L1 | 0 | 1000 | 500 | 500 | 500 | 300 | 200 |
|
|
|
L1 | 1 | 500 | 200 | 300 | 200 | 150 | 100 | 50 |
|
|
L1 | 2 | 300 | 200 | 100 | 200 |
|
|
| 100 |
|
L2 | 0 | 850 | 600 | 250 | 600 |
| 100 | 100 |
| 50 |
L2 | 1 | 250 | 150 | 100 | 150 |
| 60 | 40 |
|
|
The formula to calculate sum of BinCount:
If BinFlag = P then take sum of BinCount from all Screen(s) of that Lot
If BinFlag = F then take sum of BinCount from last Screen of that Lot
e.g.
For Lot = L1
If BinFlag = P then take sum of BinCount from all Screen(s) of Lot=L1
If BinFlag = F then take sum of BinCount from Screen=2 of Lot=L1
For Lot = L2
If BinFlag = P then take sum of BinCount from all Screen(s) of Lot=L2
If BinFlag = F then take sum of BinCount from Screen=1 of Lot=L2
When data is grouped by Lot and BinName:
Category | Device | Lot | BinName | BinFlag | BinCount |
Cat1 | Dev1 | L1 | 1 | P | 900 |
Cat1 | Dev1 | L1 | 2 | F | 0 |
Cat1 | Dev1 | L1 | 3 | F | 0 |
Cat1 | Dev1 | L1 | 4 | F | 0 |
Cat1 | Dev1 | L1 | 5 | F | 100 |
|
|
|
| Total | 1000 |
Cat1 | Dev1 | L2 | 1 | P | 750 |
Cat1 | Dev1 | L2 | 3 | F | 60 |
Cat1 | Dev1 | L2 | 4 | F | 40 |
Cat1 | Dev1 | L2 | 9 | F | 0 |
|
|
|
| Total | 850 |
It can also be represented as following:
BinName | |||||||||
Lot | Total | P | F | 1 | 2 | 3 | 4 | 5 | 9 |
L1 | 1000 | 900 | 100 | 900 |
|
|
| 100 |
|
L2 | 850 | 750 | 100 | 750 |
| 60 | 40 |
|
|
And when data is grouped by Device and BinName:
Category | Device | BinName | BinFlag | BinCount |
Cat1 | Dev1 | 1 | P | 1650 |
Cat1 | Dev1 | 2 | F | 0 |
Cat1 | Dev1 | 3 | F | 60 |
Cat1 | Dev1 | 4 | F | 40 |
Cat1 | Dev1 | 5 | F | 100 |
Cat1 | Dev1 | 9 | F | 0 |
|
|
| Total | 1850 |
See that the data is first grouped by lot and then summed for device.
Any help is appreciated. Thanks.
Anybody plz....
If the output you want is that last table, this looks like just a regular sum of BinCount. If you place those other columns into a matrix visual and add a measure for the sum, the row context will take care of the conditions for you.
Sum of BinCount = SUM(Tablename[BinCount])
Proud to be a Super User!
@KHorseman It is not what I require.
Here is the formula to calculate sum of BinCount:
If BinFlag = P then take sum of BinCount from all Screen(s) of that Lot
If BinFlag = F then take sum of BinCount from last Screen of that Lot
Correct results are highlighted with green, blue, and brown boxes. I want to know how to combine these three measure into a single measure.
Hi @javedbh
If you want to calcualte the BinCount based on BinFlag and Screen group, you can use ALLEXCEPT() as filter in CALCULATE().
=CALCULATE(SUM(Sheet[BinCount]),ALLEXCEPT(Shee2,Sheet[BinFlag],Sheet[Screen]))
Reference:
Regards,
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 |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |