Forum Discussion

GlitchedDuck's avatar
GlitchedDuck
Frequent Visitor
4 years ago
Solved

Help Grouping Counts by Company

Hi all,

 

I'm working on a dashboard to show live suppliers and want to show in a card how many suppliers are live in the database based on a count of invoices. i've added some data to show what i'm working with. So with this i want to show Company 1 ( 1 Live Supplier ) Company 2 ( 2 Live Suppliers ) and Company 3 ( 2 Live Suppliers )

 

NameInvoicesOrganization
Apple189Company 1
Microsoft24Company 2
Facebook146Company 3
SAS23Company 2
Oracle24Company 3
Dell0Company 3
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi GlitchedDuck ,

    According to your description, I create a sample.

    Here's my solution, create a measure.

    Count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Name] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Organization] = MAX ( 'Table'[Organization] )
            && SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Organization] = EARLIER ( 'Table'[Organization] )
                        && 'Table'[Name] = EARLIER ( 'Table'[Name] )
                ),
                'Table'[Invoices]
            ) > 0
    )
)

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • SergioSilvaPT's avatar
    SergioSilvaPT
    Resolver V
    4 years ago

    Hi GlitchedDuck ,

     

    You only need to create a measure with the following:

     

    Count of Name = COUNT('Table'[Name])

     

    And put the organization and the measure in a table:

     

     

     

    Regards,

    Sérgio Silva 

     
    If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
    • GlitchedDuck's avatar
      GlitchedDuck
      Frequent Visitor
      4 years ago

      Thanks for the reply, i should of said in my data its shows each indiviual invoice so the count shows Company 1 as 189, but if i group it name it gives me the table above

  • SergioSilvaPT's avatar
    SergioSilvaPT
    Resolver V
    4 years ago

    GlitchedDuck ,

     

    Adding the individual invoice it will always show one supplier per line, as one invoice can only have one supplier.

     

    You can force the measure to ignore the invoice in the filtering and show the global suppliers per Organization:

    Count of Name = CALCULATE(
            COUNT('Table'[Name]),
            ALL('Table'[Invoices]
            ))

     

    Giving something like this:

     

     

    Regards,

    Sérgio Silva 

     
    If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

     

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi GlitchedDuck ,

    According to your description, I create a sample.

    Here's my solution, create a measure.

    Count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Name] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Organization] = MAX ( 'Table'[Organization] )
            && SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Organization] = EARLIER ( 'Table'[Organization] )
                        && 'Table'[Name] = EARLIER ( 'Table'[Name] )
                ),
                'Table'[Invoices]
            ) > 0
    )
)

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.