0


I have a weird powerpivot that I'm hoping you experts could help...
The first table has agreementID and associating department, and the second table has agreementID and associating revenue (see below), I've also created a join between table1&table2 using agreementID.
Currently I have a pivot table that shows the agreement ID and total revenue (all from the second table). I created a department slicer from the first table. I was hoping this slicer would allow users to filter on the agreement based on the associating department (i.e. if "finance" is selected, only agreement 123/789 will show up).Right now, regardless of what I select in the slicer, all three agreements show up in the pivot. 

Metric:=CALCULATE(SUM('Table2'[Revenue])


What am I doing wrong and how could I fix this issue? would appreciate any help/support you can provide!

thank you,

Table 1

AgreementID | Department
========================
123         | Sales
123         | Finance
123         | Consulting
123         | Marketing
456         | Sales
456         | Consulting
456         | Marketing
789         | Sales
789         | Finance
789         | Marketing

Table 2

AgreementID | Revenue
=====================
123         | 900000
456         | 200000
789         | 400000
Sergey Lossev
  • 1,430
  • 10
  • 20
user2669043
  • 97
  • 1
  • 6
  • 12

1 Answers1

3
Metric:=
CALCULATE(
    SUM(Table2[Revenue])
    ,Table1
)

You've got an abnormal table structure wherein your fact exists on the 1 side of a 1-many relationship. In DAX, you can force context to flow "uphill" from the many to the 1 by using a table reference in CALCULATE() from the many side of the relationship.

If using Power BI Desktop you can set a filter to be bi-directional and avoid having to write measures specially to deal with this situation. This will also be present in Tabular 2016, and therefore in Excel 2016.

You should also really give your tables better, more descriptive names than Table1 and Table2.

Below is an image showing everything in my model:

enter image description here

greggyb
  • 3,728
  • 1
  • 11
  • 32
  • thanks for your response. do I have to have the formula even for excel 2016? I have Excel 2016, and I don't have an option for bidirectional filter option. in the relationship, I cannot change the way arrow <- is pointing. Having the formula have solved everything though!! and yes I have more descriptive table names in my real workbook:) – user2669043 Nov 09 '15 at 22:35
  • So, it's not in 2016 yet, but based on what I've heard and read, we can expect it to come. I can't say when exactly, but I'd put SQL Server 2016 release as an outside boundary. Excel's Power Pivot engine *is* SSAS Tabular, so it's possible they're waiting for SQL Server release to update Excel. That being said, the function is currently available in Power BI Desktop, which is the first environment to see most BI improvements in the Microsoft stack. Sorry for the confusion. – greggyb Nov 09 '15 at 22:38
  • Got it! thank you so much! I haven't yet used PowerBI Desktop as you still cannot export datamodel/views out of PowerBI Desktop to Excel. Hoping they have better excel integration so that I can start using both soon! – user2669043 Nov 09 '15 at 23:26