How to create a measure to count different numbers?

Asked

Viewed 352 times

0

I’m new in 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 an excel spreadsheet that gives me more or less to structure down.

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 correctly, 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 number of requests, for example, in this block of excel that I shared, would have 11 different requests

powerbi

  • Good afternoon Gabriel, You can use the Power BI Group By option. If you would like a reference, this page has a lot of info about this: https://docs.microsoft.com/pt-br/power-bi/desktop-common-query-tasks Abs, Rodrigo Melo

  • Buddy, it didn’t work

1 answer

3

@Rodrigomelo was right, you have to group twice:

The first 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 to add 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"
  • 1

    Buddy, with the DISTINCTCOUNT(Protocol) worked!!

Browser other questions tagged powerbi

You are not signed in. Login or sign up in order to post.