Create a chart that shows one Company against the rest

[jbrines]

Hi Guys,

I have two columns Company and Created.

The Company one has multiple Comapny Names but also My company name.

I want to be able to show a chart with a count of My Comapny Name and count of the rest.

Would also like to base these number over Monthly counts.

Many thanks.

J 

[Bibiano_Geraldo · Accepted solution]

HI @jbrines ,
To achieve your goal of creating a chart that counts Clark Contracts as one bar and all other companies as another bar, and also bases these counts on a monthly basis, you can use the bellow DAX measures:

1- Create a Measure for "Clark Contracts" Count:

ClarkContractsCount = 
CALCULATE(
    COUNTROWS('Table'), 
    'Table'[Company] = "Clark Contracts"
)

2- Create a Measure for "Rest" Count:

RestCount = 
CALCULATE(
    COUNTROWS('Table'), 
    'Table'[Company] <> "Clark Contracts"
)

Once these measures are created, you can use them in a Clustered Bar Chart:

• Drag the Month (or just use Year-Month if needed) to the Axis of the chart.
  Add the ClarkContractsCount and RestCount measures to the Values section.

Your output will look like this:

In this case is just showing one month because i used data provided by you in the comments sections.

Thank you

[jbrines]

Hi @Bibiano_Geraldo ,

How do I sort it do it is sorted by month newest to oldest?

[jbrines]

Hi @Bibiano_Geraldo ,

Worked a treat many thanks.

J

[parry2k]

@jbrines I just stored my company in a measure to make the solution scalable, in case  my company name  changes. In this case, you just need to replace "Clark Contacts" with the new value.

To keep it simple, you can always replace the "my company" measure with the fixed value "Clark Contacts" or use the new measures that I provided. I always look for the solution which is easy to manage and scalable. Cheers!

[parry2k]

@jbrines change it to this:

Measure My Company = 
VAR __MyCompany = [Measure My Company]
RETURN
CALCULATE ( 
    [Measure Count],
    KEEPFILTERS ( Table[Company] = __MyCompany  )
)

Measure Rest = 
VAR __MyCompany = [Measure My Company]
RETURN
CALCULATE ( 
    [Measure Count],
    KEEPFILTERS ( Table[Company] <> __MyCompany )
)

[parry2k]

@jbrines create the following 4 measures to achieve the result, use "Measure My Company" and "My Rest" in the visual. You can always name the measures whatever you want.

Measure My Company = "Clark Contacts"

Measure Count = COUNTROWS ( Table )

Measure My Company = 
CALCULATE ( 
    [Measure Count],
    KEEPFILTERS ( Table[Company] = [Measure My Company] )
)

Measure Rest = 
CALCULATE ( 
    [Measure Count],
    KEEPFILTERS ( Table[Company] <> [Measure My Company] )
)

[jbrines]

Hi @parry2k ,

Getting the following on messre 3 and 4.

[jbrines]

Hi @parry2k ,

See sample below.

I am looking to show a chart that two bars (Clark Contracts and the Rest).

From the sample Clark Contacts will have a count of all Clark Contracts and the rest will have a count of everything except Clark Contracts.

I hope that makes sense?

[MattiaFratello]

Hi @jbrines, please use groups

Right-click on your column 'Company', group it, use the group in the chart

Did I answer your question? Mark my post as a solution!

[jbrines]

Hi @MattiaFratello ,

We have Companies being added all the time, would they be automatically added or would we have to do it manually?

J

[MattiaFratello]

Then why not to create a new column in the Power Query Editor, if the semantic model is not too big... 

Did I answer your question? Mark my post as a solution!

[parry2k]

@jbrines how you determine which is "my company"? Can you share sample data with the expected output?