Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Countif in Power BI

Hi all,

 

I have a table in Power BI that has the concat of order number and order line and a total net value assigned for each combination. The problem is that the same combination of order number/line appears many times. I'm trying to get the total value only once for that combination of order number and line, and not the sum. I did the formula in Excel with a countif to bring back 1 as a result of only one combination, and then I multiplied by the total value; however, I haven't been able to find the equivalent to that formula in Power BI.

Here's is a ver simple example in Excel of what I'm looking for. 

 

Order no      Order line           Concat             Value           Distinct

123456              10                  12345610             5                   5

123456              20                  12345620             3                   3

123456              10                  12345610             5

123456              20                  12345620             3

123456              10                  12345610             5

123456              20                  12345620             3

123457              10                  12345710             2                   2

 

I hope someone can help me what a formula that could work in Power BI.

 

 

 

Thank you

Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    What amitchandak said is like this:

     

    1. Add an Index column in Power Query Editor.

     

    2. Create a column in Power BI Desktop Data view.

    Column =
VAR MinIndex =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER (
            'Table',
            'Table'[Order no] = EARLIER ( 'Table'[Order no] )
                && 'Table'[Order line] = EARLIER ( 'Table'[Order line] )
        )
    )
RETURN
    IF ( 'Table'[Index] = MinIndex, 'Table'[Value] )

     

     

    BTW, .pbix file attached.

     

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , You have add a index colum and then add then find the min for order no , order using earlier and put value there

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak can you please explain the answer again?
      I didn't get your answer.

      Thank you!

      • Icey's avatar
        Icey
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        What amitchandak said is like this:

         

        1. Add an Index column in Power Query Editor.

         

        2. Create a column in Power BI Desktop Data view.

        Column =
VAR MinIndex =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER (
            'Table',
            'Table'[Order no] = EARLIER ( 'Table'[Order no] )
                && 'Table'[Order line] = EARLIER ( 'Table'[Order line] )
        )
    )
RETURN
    IF ( 'Table'[Index] = MinIndex, 'Table'[Value] )

         

         

        BTW, .pbix file attached.

         

         

         

        Best Regards,

        Icey

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    6 years ago

    Anonymous 

    If you want to do it with a measure give something like this a try.

     

    Measure = 
SUMX(SUMMARIZE('Table','Table'[order nb],'Table'[order line]),CALCULATE(MAX('Table'[value])))

     

     

    *edit slight tweek to get the total correct.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      Thank you very much for your help!

      Unfortunately it didn't work, it brings back the same number to each of the rows 😞

       

       

      • jdbuchanan71's avatar
        jdbuchanan71
        Icon for Super User rankSuper User
        6 years ago

        Right, mine is meant as a measure not a calculated column.  If you need to have a calculated column the solution form amitchandak  is the way to go.

        Also, if you use my measure please note I make a slight tweek to it after I first posted it.