cancel
Showing results for
Did you mean:
Highlighted
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
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!

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!

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 146 members 1,454 guests
Recent signins: