cancel
Showing results for
Did you mean:
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:

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.

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

Paris Patris

@v-qiuyu-msft

2 REPLIES 2
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.

Best Regards,

Amy

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.

 Account date country Type Value 3. Trading Profit 7/10/2018 Austria Actual 10 3. Trading Profit 7/11/2018 Austria Actual 8 3. Trading Profit 7/10/2019 Austria Actual 12 3. Trading Profit 7/11/2019 Austria Actual 15 3. Trading Profit 7/10/2019 Austria Forecast 1 12 3. Trading Profit 7/11/2019 Austria Forecast 1 13 1. Gross Profit 7/10/2018 Austria Actual 40 1. Gross Profit 7/11/2018 Austria Actual 30 1. Gross Profit 7/10/2019 Austria Actual 50 1. Gross Profit 7/11/2019 Austria Actual 60 1. Gross Profit 7/10/2019 Austria Forecast 1 45 1. Gross Profit 7/11/2019 Austria Forecast 1 55

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,

Announcements

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

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!