How to create a measure to count distinct numbers?

Question

How to create a measure to count distinct numbers?

I'm new to this world of Power bi and I'm in need of a help, it may be basic what I'm going to ask.

I have a spreadsheet in excel that gives me more or less the structure below.

Protocolo                     Pedido       Item

ACC003.2018.PRISMA.17773959 | 5100712439 | 10
ACC003.2018.PRISMA.17773959 | 5100712439 | 20
ACC003.2018.PRISMA.17773959 | 5100712439 | 30
ACC003.2018.PRISMA.17773959 | 5100712439 | 40
ACC003.2018.PRISMA.17938855 | 5100714674 | 10
ACC003.2018.PRISMA.17938855 | 5100714674 | 20
ACC003.2018.PRISMA.17938855 | 5100714674 | 30
ACC003.2018.PRISMA.17938855 | 5100714675 | 10
ACC003.2018.PRISMA.17938855 | 5100714676 | 10
ACC003.2018.PRISMA.17938855 | 5100714677 | 10
ACC003.2018.PRISMA.17938855 | 5100714678 | 10
ACC003.2018.PRISMA.17938855 | 5100714678 | 20
ACC003.2018.PRISMA.17938855 | 5100714679 | 10
ACC003.2018.PRISMA.17938855 | 5100714679 | 20
ACC003.2018.PRISMA.17938855 | 5100714680 | 10
ACC003.2018.PRISMA.17938855 | 5100714681 | 10
ACC003.2018.PRISMA.17938855 | 5100714682 | 10
ACC003.2018.PRISMA.17938855 | 5100714683 | 10
ACC003.2018.PRISMA.17938855 | 5100714683 | 20

If you notice Well, you can see that the protocol repeats for the request, because it has an item for the request.

What I want to count is the amount of requests, for example, in this block of excel that I shared, would have 11 distinct requests

0

powerbi

Author: gabrielfalieri, 2018-10-22

Source

1 answers

score 3 · Answer 1

@ RodrigoMelo was right, you have to group TWICE:

The former can be grouped by Sum (Items) to eliminate duplicates in Pedidos.

The second has to group with two conditions one that counts the Pedidos and the second that adds the Items.

Advanced Editor (Power m code):

let
    Source = Excel.Workbook(File.Contents("C:\Users\david\Desktop\stack.xlsx"), null, true),
    Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
    #"Grouped Rows" = Table.Group(Table2_Table, {"Protocolo", "Pedido"}, {{"Item", each List.Sum([Item]), type number}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Protocolo"}, {{"Count", each Table.RowCount(_), type number}, {"Item", each List.Sum([Item]), type number}})
in
    #"Grouped Rows1"