cancel
Showing results for
Did you mean:
Frequent Visitor

## Slicer Working On Only Half a Calculation

Hey friends, I've been working on this for days and cannot figure out a solution. Any suggestions would be welcome!

Below is a mock up sample of my data. Basically I have a table with a number of companies, products, metrics, and values for the metrics.  I have been using the slicer to filter my data by company and product. It has been working great. Now the business has asked for an monthly average calculation.

I created a second table that lists the companies, products, when we started tracking data for each company (start date), and how many months they have been active with us (based off the start date). My plan was to create a measure (I thought it would be so simple!) My measure was Avg Admits = SUM(table1.[Admits])/SUM(table2.[Active Months]). I used SUM on the Active Months because it made me use an aggregate and I assumed it wouldn't matter since the slicer would be filtering by company and product so it would be the SUM of a single number.

The slicer recognizes table1.[Admits] and sums the correct number of admits according to company and product, however no matter what I try I cannot get the slicer to filter the table2.[Active Months] correctly. It SUMs for the entire table, COUNTs for the entire table, DISTINCTCOUNT, MAX, etc.  Why won't the slicer work on Active Months?! I tried creating relationships for my tables, but as the company/product combination creates the unique identifier, I cannot create relationships. I tried just putting [Admits] in the visualization and having Power BI Average it, but it divides it by 12. I need it to average based on how many months the company/product has been active. I could get it to divide by 10 by filter is not 0, which works for companies/products which have been active all year and and don't have a month with 0 admits. But that's not very helpful or accurate.

 Table1 Company Product Period Admits A 1 Jan 1 A 1 Feb 5 A 2 Jan 2 A 2 Feb 3 A 3 Jan 9 A 3 Feb 0 B 1 Jan 1 B 1 Feb 4 B 2 Jan 3 B 2 Feb 7 B 3 Jan 8 B 3 Feb 5 C 1 Jan 3 C 1 Feb 2 C 2 Jan 5 C 2 Feb 9 C 3 Jan 7 C 3 Feb 2 A 1 Mar 1

 Table2 Company Product Start Date Active Months A 1 1/1/2017 10 A 2 1/1/2017 10 A 3 5/1/2017 6 B 1 4/1/2017 7 B 2 4/1/2017 7 B 3 NULL 0 C 1 8/1/2017 3 C 2 NULL 0 C 3 10/1/2017 1

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

## Re: Slicer Working On Only Half a Calculation

I figured it out! Just in case this can help anyone... The slicer seems to only work on a single table. So even though both my tables had Company and Product fields, the slicer wasn't recognizing the fields from Table 2. I added my Active Months column to my Table1, created a calculated column AVG = Admits/Active Months and it works!