Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
colsand
Frequent Visitor

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 101,617
  11 - 20588
  21 - 30600
  31 - 50842
  51 - 90776
    > 90617
Negative LT14
Total5,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

1 ACCEPTED SOLUTION

@colsand

 

Please see your updated file here

 

Now you can use the Customers as filter/slicer


Regards
Zubair

Please try my custom visuals

View solution in original post

12 REPLIES 12
BeemsC
Resolver III
Resolver III

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)StartEnd
Less Than 10010
  11 - 201120
  21 - 302130
  31 - 503150
  51 - 905190
    > 9090200
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]
    )
)

 

 


Regards
Zubair

Please try my custom visuals

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


Regards
Zubair

Please try my custom visuals

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]
    )
)

 


Regards
Zubair

Please try my custom visuals

@colsand

 

Please see your updated file here

 

Now you can use the Customers as filter/slicer


Regards
Zubair

Please try my custom visuals

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

 

 


Regards
Zubair

Please try my custom visuals

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

@colsand

 

You will find the following article by champs @AlbertoFerrari and Marco Russo very useful

 

http://www.daxpatterns.com/static-segmentation/


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.