Forum Discussion
Solved
ANALYSIS TABLE/COUNTX FUNCTION
I am trying to create a small analysis table which gives a breakdown of the number of items which fall within certain lead times.. In excel, this would be a simple matter of using COUNTIF/COUNTIFS bu...
Zubair_Muhammad
Community Champion
Community ChampionHi colsand
Such segmentation/ banding is easily achieved using a PARAMETER TABLE in Power BI
See the attached file using your sample data
Here are the steps
Create a parameter table with segements you desire
| Cluster LT (days) | Start | End |
| Less Than 10 | 0 | 10 |
| 11 - 20 | 11 | 20 |
| 21 - 30 | 21 | 30 |
| 31 - 50 | 31 | 50 |
| 51 - 90 | 51 | 90 |
| > 90 | 90 | 200 |
| Negative LT | -1000 | -1 |
Then you can add a Calculated Column for # of items within each segment/band as follows
# Line Items_ =
CALCULATE (
COUNT ( MainTable[Line Items] ),
FILTER (
MainTable,
MainTable[LT (days)] >= Parameter_Table[Start]
&& MainTable[LT (days)] <= Parameter_Table[End]
)
)
Many thanks for your help. I will give this a go this afternoon. Unfotunately, my company's web security policy has blocked access to the attachment, so I will have to get this sorted out.
Regards.
Colin