Creating a measure for same metrics but different dimensions

[dogbob]

I have a collection of tables that have various cuts of the same metrics using different dimensions. Simplified example:

Is there a way I can create a single measure to calculate the average cost per lead, regardless of the dimensions used? In other words, one measure to divide Ad Spend by Leads whether I'm viewing it by Region, Message, or Device.

In practice, I have many more tables each needing the same set of ~5-10 measures, and would like to avoid the clutter and potential confusion caused by having nearly duplicate measures (i.e. cost per lead [Region], cost per lead [Device], etc).

Thanks for your time!

[Anonymous]

Hi  @dogbob ,

Thanks for the reply from @amitchandak , please allow me to provide another insight: 

Here are the steps you can follow：

1. Use Enter data for text input - used as a slicer.

2. Create measure.

Measure =
SWITCH(
    TRUE(),
    MAX('Slicer_Table'[Slicer])= "Region",DIVIDE(SUMX(ALL('Table1'),'Table1'[Leads]),SUMX(ALL('Table1'),[Ad Spend])),
    MAX('Slicer_Table'[Slicer])= "Message",DIVIDE(SUMX(ALL('Table2'),'Table2'[Leads]),SUMX(ALL('Table2'),[Ad Spend])),
    MAX('Slicer_Table'[Slicer])= "Device",DIVIDE(SUMX(ALL('Table3'),'Table3'[Leads]),SUMX(ALL('Table3'),[Ad Spend])))

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@dogbob , Add one additional column in all table Dimension name and then append them in power query

and then you can have one common measure

Divide(Sum(Table[Ad Spend]), Sum(Table[Leads]) )

[dogbob]

Thanks for the suggestion -- I had considered this option, but isn't appending tables with different dimensions generally considered a bad practice, since it results in columns that contain mostly blank values? For example, using the same data as above, 2/3rds of the region column would be null, since 2 of the 3 tables don't have any data associated with region.