Exporting matrix to excel but total percentage is not correct

[wpf_]

Hi I have a matrix in power bi that shows counts of cars on particulate dates and compares the counts from a previous year.  Then i have another column that shows the percentage difference of relating years.  It all shows up correctly in the matrix but when i export it to excel, and when I put together the pivot table, the grand total percentage difference does not show up correctly.  But the grand total counts do and the percentage should be based on the counts, so i'm not sure why it's not showing up correctly.  The percentage shows up correctly outside of the grand totals.  

[amitchandak]

@wpf_ , can you share measure calculations

Can you share sample data and sample output in table format?

[wpf_]

@amitchandak ,

Let me recreate it with a sample data set and send it to you.  I posted the measure calculations above in the reply to another. Thanks for your help. 

[parry2k]

@wpf_ are you adding this as a measure, can you change this

=SUMX(
VALUES('Traffic Data'[specificLocation] ),
VAR PrimarySum =
CALCULATE( SUM('Traffic Data'[carCountMethod1] ) + SUM( 'Traffic Data'[carCountMethod2]) )
RETURN
IF( MAX('Traffic Data'[specificLocation]) = 50, PrimarySum *2 , PrimarySum )
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[wpf_]

@parry2k ,

I tried but it didnt work. same results.   

[parry2k]

@wpf_ what are the measures? How you are calculation previous year value?

[wpf_]

@parry2k ,

the measures are as follow:

Current:  //I have two ways to get car counts.  And I check if the counts are from location "50", I double the counts. 

=SUMX(

VALUES('Traffic Data'[specificLocation] ),

VAR PrimarySum =

CALCULATE( SUM('Traffic Data'[carCountMethod1] ) + SUM( 'Traffic Data'[carCountMethod2]) )

RETURN

IF( 'Traffic Data'[specificLocation] = 50, PrimarySum *2 , PrimarySum )

Prev:  //I calculate the same data from a year ago (364 days ago to get to the exact day of week (mon, tues etc)).

=CALCULATE([Current], DATEADD('Date'[Date], -364, DAY))

%:   //the percentage change between years. 

= DIVIDE([Current]-[Prev], [Prev])

As I said, the calculations look good in power bi, only when I export it to excel in pivot table the grand totals for the percentage is off.  Thanks.