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
Anonymous
Not applicable

add measure table into a matrix table as a separate column

Dear All,

 

I would like to request for your support on the below topic. What I am trying to do is to create a matrix table where are it will be like below 2 tables in one:Capture.PNG

 

All the values are messures in both tables. What I would like to do is to add a new column called number 5. Conv. Rate and put under it the measures which are on the second table of the screenshot. My info are like below and the measures are coming all from the column of value.

Capture 1.PNG

 

Let me know if you need more info and I can provide everything.

 

Thank you very much for your support in advance,

 

Paris Patris

 

@v-qiuyu-msft

2 REPLIES 2
v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

>>What I would like to do is to add a new column called number 5. Conv. Rate and put under it the measures which are on the second table of the screenshot.

 

I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

 

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

 

Best Regards,

Amy

Anonymous
Not applicable

Dear Amy/All,

 

I am attaching you a simple sample of my exercise with random values.

 

AccountdatecountryTypeValue
3. Trading Profit7/10/2018AustriaActual10
3. Trading Profit7/11/2018AustriaActual8
3. Trading Profit7/10/2019AustriaActual12
3. Trading Profit7/11/2019AustriaActual15
3. Trading Profit7/10/2019AustriaForecast 112
3. Trading Profit7/11/2019AustriaForecast 113
1. Gross Profit7/10/2018AustriaActual40
1. Gross Profit7/11/2018AustriaActual30
1. Gross Profit7/10/2019AustriaActual50
1. Gross Profit7/11/2019AustriaActual60
1. Gross Profit7/10/2019AustriaForecast 145
1. Gross Profit7/11/2019AustriaForecast 155

 

The measures that I have used on the above screenshot are the below

CY (Current Year)= CALCULATE(sum(Financials[Value]),Financials[# PnL Ops.Measure Type]="actual")

F1 (Forecast)= CALCULATE(sum(Financials[Value]),Financials[# PnL Ops.Measure Type]="Forecast 1")

PY (Last year) = CALCULATE([Current Year], SAMEPERIODLASTYEAR('date'[Date]),Financials[# PnL Ops.Measure Type]="Actual")

Vs. PY (prior year)= (IFERROR(CALCULATE([Current Year]/[Last Year]-1,Financials[# PnL Ops.Measure Type]="Actual"),0))

 

Financials is the name of my table data. And I have create a calendar table as well calling it date. the Conversion Rate is coming by the calculation below (Trading Profit/Gross Profit).

 

Conv. Rate Actuals =

Var GP = CALCULATE([Current Year], Financials[Accounts]="1. Gross Profit")

Var TP = CALCULATE([Current Year],Financials[Accounts]="3. Trading Profit")

Return TP/GP

 

The goal is to put the measure of Conv. Rate a new column 5 so it can be next to them other.

 

Let me know if that helps. if not still let me know how can I help you more on that.

 

Thank you very much for all your help on that.

 

Cheers,

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.