Forum Discussion
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 but i am struggling to re-create this in Power BI. I've tried using the COUNTX function, but although this doesn't give me an error message when I create the measure, it does when I try and put this in a visual.
I'm aware that there is probably a better way to achieve what I'n trying to do, so would appreciate some guidance about the best way to do this.
This is the output I am trying to achieve:
| Cluster LT (days) | # Line Items | ||
| Less Than 10 | 1,617 | ||
| 11 - 20 | 588 | ||
| 21 - 30 | 600 | ||
| 31 - 50 | 842 | ||
| 51 - 90 | 776 | ||
| > 90 | 617 | ||
| Negative LT | 14 | ||
| Total | 5,054 |
What I am trying to do is count the number of items which have lead times in these categories.
Any help would be greatly appreciated.
TIA.
Colin
12 Replies
Hey,
I don't think you want to use the COUNTIF function for this specific problem.
The better alternative is probably to use CALCULATE or IF depending on your preference.
A measure for the date 11-20 would probably look something like the following:Measure for 11-20 = CALCULATE(SUM(Table1[Amount]);Table1[Days] = "11-20")
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
