Forum Discussion

thorpyuk's avatar
thorpyuk
Icon for Helper III rankHelper 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... [Prodc...
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]
        )
    )

     

thorpyuk's avatar
thorpyuk
Icon for Helper III rankHelper 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

camargos88's avatar
camargos88
Icon for Community Champion rankCommunity Champion
6 years ago

thorpyuk ,

 

Can you provide some data as example ?

 

Ricardo

  • thorpyuk's avatar
    thorpyuk
    Icon for Helper III rankHelper III
    6 years ago

    Hi Camargos, here's the layout of my sales table ( db_v_SalesProduct ) and my site table ( db_v_LookupSite ).

    They appear to link on the [TerminalID] field, but as i say, each site will have multiple terminals.

    How do i share a file with data?

     

    • camargos88's avatar
      camargos88
      Icon for Community Champion rankCommunity Champion
      6 years ago

      thorpyuk ,

       

      You can use onedrive, google drive, dropbox...

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Ah ok, I see. So each SiteID can have 1 or more TerminalIDs, and you want the measure to return the number of distinct SiteIDs for each ProdCode. Try this

         

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