cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ppatris Regular Visitor
Regular Visitor

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
Community Support Team
Community Support Team

Re: add measure table into a matrix table as a separate column

Hi @ppatris ,

 

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

ppatris Regular Visitor
Regular Visitor

Re: add measure table into a matrix table as a separate column

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
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors