cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
afrank Frequent Visitor
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.

 

Is the slicer malfunctioning? Am I going about this wrong? PLEASE HELP!

 

Table1
CompanyProductPeriodAdmits
A1Jan1
A1Feb5
A2Jan2
A2Feb3
A3Jan9
A3Feb0
B1Jan1
B1Feb4
B2Jan3
B2Feb7
B3Jan8
B3Feb5
C1Jan3
C1Feb2
C2Jan5
C2Feb9
C3Jan7
C3Feb2
A1Mar1

 

Table2
CompanyProductStart DateActive Months
A11/1/201710
A21/1/201710
A35/1/20176
B14/1/20177
B24/1/20177
B3NULL0
C18/1/20173
C2NULL0
C310/1/20171

 

1 ACCEPTED SOLUTION

Accepted Solutions
afrank Frequent Visitor
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! Smiley Happy

1 REPLY 1
afrank Frequent Visitor
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! Smiley Happy

Helpful resources

Announcements
Virtual Launch Event

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.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 307 members 3,274 guests
Please welcome our newest community members: