New calculated column in a new table

[mkwiatkowski]

Hello everyone,

Newbie to Power BI here so hoping someone might be able to help me out with this one.

Quick summary of what i want to do is to is to take a couple of columns that i have in one table relating to aircraft and dates, do a sum total of each family of aircraft grouping by date and aircraft family and then put all of that in a new separate table.

Example:

Table with columns AircraftFamily and Date (the date format has to be like this and can't be changed). The columns of data i have have thousands of rows so this example is much smaller dataset plus the data doesn't run nice and sequentially like in the example

AircraftFamily	Date
A320 Family	April 2021
A320 Family	April 2021
A320 Family	May 2021
A320 Family	May 2021
A320 Family	May 2021
737 Family	April 2021
737 Family	April 2021
737 Family	April 2021
737 Family	May 2021
737 Family	May 2021
A330 Family	April 2021
A330 Family	April 2021
A330 Family	May 2021
A330 Family	May 2021
A330 Family	May 2021
A330 Family	June 2021

What i want the new table to look like:

Aircraft Family	Date	Total
A320 Family	April 2021	2
A320 Family	May 2021	3
737 Family	April 2021	3
737 Family	May 2021	2
A330 Family	April 2021	2
A330 Family	May 2021	3
A330 Family	June 2021	1

Hopefully someone can guide me on this one 

Regards,

Mark

[v-xulin-mstf · Accepted solution]

Hi @mkwiatkowski,

Try measure as:

Total = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALL('Table'),
        'Table'[AircraftFamily]=MAX('Table'[AircraftFamily]) && MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))
    )
)

Here is the output:

Here is the demo, please try it.

Best Regards,

Link

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

[amitchandak]

@mkwiatkowski , In table visual, take these three columns as un summarized and take again one of them as count

or create a measure and use that count(Table[Date])  and use with other three