cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## Matrix: how to add a (dynamic) Calculated Column ?

Hi all,

I created a matrix:

City | Confirmed | Cancelled

-------------------------------------------------

Berlin | 1.560.452 | 785.542

London | 1.102.210 | 425.254

Rows: City

Columns: JobStatus

Values: JobPrice

Now I want to add a calculated column at the end (after Cancelled colum) with the formula:

Rate = confirmed-column / cancelled-column

Result should be:

City | Confirmed | Cancelled | Rate

-------------------------------------------------

Berlin | 1.560.452 | 785.542 | 1,9864

London | 1.102.210 | 425.254 | 2,5918

Could you help me please?

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

## Re: Matrix: how to add a (dynamic) Calculated Column ?

Hello you can create seperate Measures:

Canceled=COUNTROWS(FILTER(DataTable,[Status)="Canceled"
Confirmed=COUNTROWS(FILTER(DataTable,[Status)="Confirmed"

Ratio=DIVIDE([Confirmed],[Canceled])

Maybe you could create a table directly out of your data, but I'm not familiar with this.

It would be helpful to know more about your DataTable structure for further support.

Best regards.

5 REPLIES 5
Established Member

## Re: Matrix: how to add a (dynamic) Calculated Column ?

Hello just add a calculated colum

Rate=DIVIDE([Confirmed],[Cancelled])

Best regards.

Frequent Visitor

## Re: Matrix: how to add a (dynamic) Calculated Column ?

But where do I have to add these column? In the matrix diagram or in the data table?

Take a look at the screenshots please:

Thanks

Established Member

## Re: Matrix: how to add a (dynamic) Calculated Column ?

Hello you can create seperate Measures:

Canceled=COUNTROWS(FILTER(DataTable,[Status)="Canceled"
Confirmed=COUNTROWS(FILTER(DataTable,[Status)="Confirmed"

Ratio=DIVIDE([Confirmed],[Canceled])

Maybe you could create a table directly out of your data, but I'm not familiar with this.

It would be helpful to know more about your DataTable structure for further support.

Best regards.

Frequent Visitor

## Re: Matrix: how to add a (dynamic) Calculated Column ?

Great! You did it !!!

My way:

xCountJobsCanceled = COUNTROWS(FILTER(Tabelle1;Tabelle1[Job.Status]="Canceled") )

xSumJobsCanceled = SUMX(FILTER(Tabelle1; Tabelle1[Job.Status]="Canceled");Tabelle1[Job.Total])

xSumJobsConfirmedCanceledRatio = DIVIDE([xSumJobsConfirmed];[xSumJobsCanceled])

xSumJobsConfirmedCanceledRatioText = FORMAT([xSumJobsConfirmedCanceledRatio];"0.#") & " : 1"

Highlighted
Established Member

## Re: Matrix: how to add a (dynamic) Calculated Column ?

You're welcome.

I would appreciate my post to be marked as answer.

Best regards.

## Helpful resources

Announcements

#### Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

#### Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors