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.
Is the slicer malfunctioning? Am I going about this wrong? PLEASE HELP!
| 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 |
