Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jup0
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

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.

View solution in original post

5 REPLIES 5
Floriankx
Solution Sage
Solution Sage

Hello just add a calculated colum

 

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

 

Best regards.

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:

 

Unbenannt.PNG

 

Unbenannt2.PNG

 

Thanks

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.

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"

You're welcome.

 

I would appreciate my post to be marked as answer.

 

Best regards.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.