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?

[Bibiano_Geraldo]

hi @jbrines ,

To sort, select your chart, click on 3 dots, and go to sort axis and choose to sort descending, and make sure that is sorted by month also:

[jbrines]

That doesn't seem to sort it in months it sorts it in alphabethical order.

[jbrines]

Hi @Bibiano_Geraldo any ideas on how to resolve?

Thanks

J

[Bibiano_Geraldo]

Hi @jbrines ,

Sorry to be late, i lost your previous reply, please @me to not lose any reply.

Lets dive right in.

To achieve your goal, you need to create a calculate column for month names and other calculated column to sort this months, just follow this steps:

1- Make sure that you have a calendar table, if not, just create with this DAX:

Calendar = 
CALENDAR(
    MIN(financials[Date]), //please refer your Created Date column here
    MAX(financials[Date]) //please refer your Created Date column here
)

This should in a table like this:

2- Now, In your calendar table create a column for Month Name by this DAX:

Month = FORMAT('Calendar'[Date],"Mmmm")

3- And another calculated column for Month No:

Month No = VALUE(FORMAT('Calendar'[Date],"m"))

At this point, you table should look like this:

4- Select your Month Name column and sort by Month No as shown bellow:

5- Mark the table as calendar table by right click on top of calendar table:

now enable mark as  date table and from dropdown choose date column and click in Save:

6- Create a relationship between your table and calendar table using date column:

Now in your visual, use the Month Name in X-Axis.

[jbrines]

Hi @Bibiano_Geraldo 

When you say "Now in your visual, use the Month Name in X-Axis" do you man the month name from the Calander Table as I can't get it working from either.

John

[Bibiano_Geraldo]

Yes, the Month from calendar table

[jbrines]

Hi @Bibiano_Geraldo that doesn't work see below.

[Bibiano_Geraldo]

can you share the picture of your calendar table?
Are these tables related?

its possible to share no sensitive file to check closer?

[jbrines]

@Bibiano_Geraldo  see below.

[Bibiano_Geraldo]

At this point look great, si possible to upload a no sensitive file to check what is causing this on your repor?

[jbrines]

@Bibiano_Geraldo can I send it via email? If you PM me an email address?

[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!