Forum Discussion

colsand's avatar
colsand
Frequent Visitor
8 years ago
Solved

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 bu...
Need Help
Tips and Tricks
BeemsC's avatar
BeemsC
Resolver III
8 years ago

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")
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

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

     

     

    • colsand's avatar
      colsand
      Frequent Visitor
      8 years ago

      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's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Hi colsand

         

        Could you share your file please?

        Or

        Provide sample data and desired results

    • colsand's avatar
      colsand
      Frequent Visitor
      8 years ago

      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