Forum Discussion

thorpyuk's avatar
thorpyuk
Helper III
6 years ago
Solved

Count unique orderers

Hi All,

I have a sales table, and a site table, linked on [TerminalID] which is the till number. I want to count the unique ordering sites for each individual product that might be ordered... [Prodcode] is my product ID

Thanks in advance!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    You can create the relationship in the Model pane of in Power BI (see image).

     

     

    If you are unable to create the relationship, then your measure will need to use the LOOKUPVALUE. Try this:

    Ordering Sites = 
    COUNTROWS(
        SUMMARIZE(
            ADDCOLUMNS(
                'Sales',
                "SiteID", LOOKUPVALUE(Terminal[SiteID], Terminal[TerminalID], Sales[TerminalID])
            ),
            [SiteID]
        )
    )

     

18 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Create a measure with this formula:

    Ordering Sites = COUNTDISTINCT('Sales'[TerminalID])

    Then add this measure to a table or matrix visual. When you also add ProdCode to the visual, the distinct count will be broken down by ProdCode.

    • thorpyuk's avatar
      thorpyuk
      Helper III
      6 years ago

      Hi EylesIT, thanks for your reply - that does give me unique terminals, but each site may have 3-4 different terminalID's... i wanted unique sites

    • camargos88's avatar
      camargos88
      Community Champion
      6 years ago

      thorpyuk ,

       

      Have you tried to count by the id for the site table ?

       

      Something like:

       

      DISTINCTCOUNT(ID_SITE_TABLE)

       

      Ricardo

      • thorpyuk's avatar
        thorpyuk
        Helper III
        6 years ago

        Hi Camargos88, yes that just gives me the total number of distinct sites (56) against each product, but i want only the number of those sites that have ordered