Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Adding measure from different table that are calculated with column from another table

Hi All,

I am trying to create a master table by unioning three tables- Table A, Table B and Table C. I want to calculate a measure by addidng measure from the three tables but when I add them it is not giving me the right output

 

 Table A, Table B, Table C have similarColumns and Measures. All the measures are down to date,rProperty and Channel level

Columns:

Date

Revenue

Property

Channel

Measures:

GP (Sum(TableA[Revenue]) - Sum(Spend[Spend])). Spend comed from Spend Table

MTD Gross Profit (Month to Date Gross Profit)

 

CALCULATE (

(SUM ( 'TableA'[Revenue] )- sum(Spend[Spend])), GROUPBY('Channel','Channel'[Channel]),
FILTER (ALLSELECTED('Date'),
'Date'[Date] <= MAX ( 'TableA'[Date])
)

Gross Profit Run Rate (MTD Gross Proft/Running Daily Pacing)

 

Running Daily Pacing is a measure from  another table called Daily Pacing. There are three Daily Pacing tables each for  Table A , B and C . It has following columns:

Date,

Property,

Daily Pacing

Measure: 

Running Daily Pacing
  CALCULATE (
SUM ( 'Daily Pacing TableA'[TableA Daily Pacing]),
FILTER (ALLSELECTED('Daily Pacing TableA'),
'Daily Pacing TableA'[Date] <= MAX ( 'Daily Pacing TableA'[Date])
)
Daily Pacing and Running Daily Pacing is at Date and Property level
 
Spend table has following columns
Date,
Channel.
Property,
Spend

 Spend is at Date, Property and Channel level

 

I have bridge tables- Date, Channel, Property to create relationship amongst tables

 

I Unioned Table A , Table B and Table C with following Columns- Date,Property, Channel, Revenue

I am trying to create a measure TableMain[Gross Profit Run Rate]=TableA[Gross Profit Run Rate] + Table B [Gross Profit Run Rate]+TableC[Gross Profit Run Rate]   but is not calculating correctly 

 

I basically want to show sum of these Run Rate measure at Total, Property, channel level. Any help with the formula would be much appreciated!

             

 

I have tried unioning Gross Profit Run Rate as well but it says it detects circular dependency. I have tried unioning thre daily pacing tables and using the same formula to create Gross Profit Run Rate measure (as shown for Table A) but that does not work either.

 

Thanks!

 

 

1 Reply

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar

    Hi Anonymous,

     

    It seems that you have several tables in your scenario.

     

    If it is convenient, could you please share a dummy pbix file which can reproduce the scenario and your desired output, so that we can help further investigate on it? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.)

     

    Best Regards,

    Cherry