Countif in Power BI

[Anonymous]

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

[jdbuchanan71]

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

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]

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.

[amitchandak]

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

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

Thank you!

[Icey · Accepted solution]

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.

[Anonymous]

@Icey thank you SO much for your help. It finally worked!! 🙂