Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Thank you very much for your support in advance,
Paris Patris
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
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,
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |