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...
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.

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.