Forum Discussion

matt_r_p's avatar
matt_r_p
New Member
3 years ago
Solved

Filter context on a card visual

Hello, this must be a very easy question but despite reading lots of pages I am stumped. 

 

I am trying to use a measure to divide two other measures but it won't keep the filter context on the total. In the image below, I want to divide Total Sales by Total FOH Hours to get FOH Productivity per site. Instead, it is giving me the grand total of Total Sales divided by Hours for each Site. How do I get Power BI to give me Total Sales By Site divided by Hours Per Site - without me having to create a measure for each one (such as "site = DSQ")? I have a site list and a date list as separate tables, with relationships set up between the sales data and the rota data.

 

Here's the relationships. Tevalis Reports is the sales data, Planday data is the hours worked.

 

The productivity measure I've got is 

FOH Productivity = divide ([Total Sales], [Total FOH Hours])
 
Grateful for help, thank you.
  • matt_r_p's avatar
    matt_r_p
    3 years ago

    Thanks. 

     

    In fact I had a thought overnight and solved it. The issue was that in the FOH Productivity table, I was using 'Site' as the filter from the Planday reports table and not the SiteList table, and it wasn't carrying the filter through to the TevalisReports sales data. Perhaps in future it would be good practice to give a different name to a field in a dim table such as SiteList to avoid this confusion?

2 Replies

  • Shaurya's avatar
    Shaurya
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi matt_r_p,

     

    Instead of using:

     

    FOH Productivity = DIVIDE([Total Sales],[Total FOH Hours])

     

    Use:

     

    FOH Productivity = SUM('Tevalis Report'[Sales]/'Tevalis Report'[FOH Hours])

     

    Works for you? Mark this post as a solution if it does!

    • matt_r_p's avatar
      matt_r_p
      New Member
      3 years ago

      Thanks. 

       

      In fact I had a thought overnight and solved it. The issue was that in the FOH Productivity table, I was using 'Site' as the filter from the Planday reports table and not the SiteList table, and it wasn't carrying the filter through to the TevalisReports sales data. Perhaps in future it would be good practice to give a different name to a field in a dim table such as SiteList to avoid this confusion?