0

I have a table that includes columns for accountID, revenue, quarter and estimated revenue. the issue with this table is that the revenue repeats for each quarter. The table is perfect if I'm trying to find the estimate, but as soon as I sum the revenue, it basically quadruples for each account. I can divide by 4, but that gives a wrong revenue number for each quarter. Is there a DAX function that allows me to show "4000" for each quarter, but at account/company level, it would not quadruple in size?

AccountID | Revenue | Quarter | Estimate
123 | 4000 |Q1 | 4000
123 | 4000 |Q2 | 5000
123 | 4000 |Q3 | 2000
123 | 4000 |Q4 | 4000
456 | 3000 |Q1 | 4000
456 | 3000 |Q2 | 5000
456 | 3000 |Q3 | 1000
456 | 3000 |Q4 | 3000

What I would like to see in pivot

Account ID | Quarter | Sum of Revenue | Sum of Estimate 123 | Q1 |4000 | 4000
123 | Q2 |4000 | 5000
123 | Q3 |4000 | 2000
123 | Q4 |4000 | 4000
123 Total |4000 | 15000

456 | Q1 |3000 | 4000
456 | Q2 |3000 | 5000
456 | Q3 |3000 | 1000
456 | Q4 |3000 | 3000
456 Total |3000 | 13000
Grand Total |7000 | 2800

user2669043
  • 97
  • 1
  • 6
  • 12
  • What visualization are you using? If you don't want the column be summarized set the `Don't Summarize` option. – alejandro zuleta Sep 22 '16 at 16:34
  • I'm using just the regular pivot table on top powerpivot. I want it to Summarize, just not quadruple the amount. So if I have AccountID, Quarter, Revenue, Estimate. I'm ok with Revenue to be repeated for each quarter, but when I have multiple accounts, I want the sum to be simply revenue of those two accounts, not sum of revenue 4 quarters x 2 accounts. – user2669043 Sep 22 '16 at 16:51
  • Do you have accountID in rows and Revenue in values? Note pivot tables group the repeated values in rows. – alejandro zuleta Sep 22 '16 at 19:53
  • I understand that's the default, I'm asking whether I could have DAX formula that allows me to not group repeated value. Edited OP to show what I need – user2669043 Sep 22 '16 at 20:20

1 Answers1

0

Create two measures EstimateMeasure and RevenueMeasure:

EstimateMeasure = SUM(Table1[Estimate])

RevenueMeasure = SUMX(VALUES(Table3[Revenue]),Table3[Revenue])

Add previously created measures to Values and AccountID & Quarter in your pivot table.

It should produce the following output in Power BI.

enter image description here

Note I don't have access to PowerPivot in this moment, so I can't post the complete table with subtotals. But you can add subtotals in PowerPivot to get the total per AccountID.

Let me know if this helps.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48