How to summarize a table showing total days by number of vehicles needed

[CMaxwell]

Self-taugh and new to Power BI.  No experience with measures, not sure if one is needed here, but I wanted to see if someone could help.  

I have a report that shows total number of vehicles needed by date for my organization.  I am trying to summarize the data to show the total number of days associated with each distinct count of vehicle need. 

For example: how many total days were 12 vehicles needed, how many total days were 11 vehicles needed, etc...  I have this information presented in a table in my report, but I would like to show the summary results where the vehicle count shows on the x-axies and the total days shows on the y-axis.  I've included a visual from an old excel report I used to use below the table for reference.  

Is there a way I can summarize the number of days by vehicle need to show in a column chart?

Thank you.

[Anonymous · Accepted solution]

Thanks for SachinNandanwar and Ashish_Mathur's concern about this issue.

Hi @CMaxwell ,

I am not sure how your source dataset is designed, below is my example.

Below is my source dataset.

Vehicle count table

Add the following measure to the Y-axis of the column chart.

Total days based on vehicle needs = 
VAR _tb =
    SUMMARIZECOLUMNS ( 'Table'[Dirve Date], "Count", COUNT ( 'Table'[Drive ID] ) )
RETURN
    COUNTX (
        FILTER ( _tb, [Count] = SELECTEDVALUE ( 'Vehicle count'[Count] ) ),
        'Table'[Dirve Date]
    )

The end result is as follows and hopefully meets your needs.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[SachinNandanwar]

You can try the SUMMARIZE or SUMMARIZECOLUMN function

https://www.sqlbi.com/articles/introducing-summarizecolumns/