Forum Discussion
ANALYSIS TABLE/COUNTX FUNCTION
Resolver IIIHey,
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")
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]
)
)
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
- Zubair_Muhammad
- Zubair_Muhammad
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
