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|
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.
Solved! Go to Solution.
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")
Such segmentation/ banding is easily achieved using a PARAMETER TABLE in Power BI
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|
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?
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] ) )
I noticed that some of your Lead Times fall in none of the Clusters. For example Leadtime greater than 200
You can always go back and revise the Start and End ranges of your Parameter Table or Add new cluster
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.