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...
Hi 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]
)
)
I have got this to work, but it gives me totals based on all of the data in the table. I have the rest of the report filtered by customer. How do I make this filter apply to the parameter table?
Thank you.
Regards.
Colin
Hi colsand
The solution to this is to go other way round
Instead of Pulling data from LT Data into Parameter Table..... Pull the Cluster name from Parameter Table into LT Data
Create a calculated Column in LT Data as follows
Cluster LT = CALCULATE ( VALUES ( Parameter_Table[Cluster LT (Days)] ), FILTER ( Parameter_Table, 'LT Data'[Lead Time] >= Parameter_Table[Start] && 'LT Data'[Lead Time] <= Parameter_Table[End] ) )