Forum Discussion

dogbob's avatar
dogbob
Regular Visitor
2 years ago

Creating a measure for same metrics but different dimensions

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!

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      dogbob
      Regular Visitor
      2 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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