Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
Solved! Go to Solution.
Please see your updated file here
Now you can use the Customers as filter/slicer
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
Hi @colsand
Could you share your file please?
Or
Provide sample data and desired results
Hi,
I hope this link is OK. https://onedrive.live.com/redir.aspx?cid=727fdb9e098e6580&resid=727FDB9E098E6580!17781&parId=727FDB9...
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] ) )
Please see your updated file here
Now you can use the Customers as filter/slicer
Many thanks for your help, that appears to be what I need.
Regards.
Colin
Hi @colsand
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
Yes, thank you, I will set the end point way over the maximum to allow for this.
Regards.
Colin
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
You will find the following article by champs @AlbertoFerrari and Marco Russo very useful
http://www.daxpatterns.com/static-segmentation/
User | Count |
---|---|
93 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
142 | |
112 | |
73 | |
55 |