cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cmn Member
Member

Getting different results in Excel and Power BI

I have an on premise SSAS tabular model that has some MTD measures.  When I build a report in Power BI Desktop, I am getting different results as compared to Excel pivot tables in some scenarios.

 

My model has an Invoice fact table, with a NetRevenue measure.  One of the columns on the fact table is called Segment. The model also has a date dimension table.

 

I have created the following measures:

 

MTD Net Revenue:=CALCULATE(sum(Invoice[NetRevenue]), DATESMTD('Date'[FullDate]))

My fact table has the following:

 

DateSegmentNetRevenue
01-Sep-2016Segment A100
10-Sep-2016Segment B100
15-Sep-2016Segment C100
20-Sep-2016Segment A100
20-Sep-2016Segment B100
20-Sep-2016Segment C100
30-Sep-2016Segment C100

 

When I create a pivot table in excel, and put a filter of 30-Sep-2016, I get the following:

 

 MTD Net Revenue
Segment A200
Segment B200
Segment C300

 

 

When I create a similar table in Power BI, I get the following:

 

 MTD Net Revenue
Segment C300

 

The reason for this is that there is only data on the 30-Sep-2016 in the fact table for Segment C.  The date filter is excluding the other 2 segments from the report.

 

Any help with this would be greatly appeciated.  I'm happy to provide more details if you need it.

 

Thanks.

3 REPLIES 3
ankitpatira Super Contributor
Super Contributor

Re: Getting different results in Excel and Power BI

@cmn In this case power bi report is right then as you've applied date filter to 30-sep and there is only one segment against that date which is returned by power bi report.

cmn Member
Member

Re: Getting different results in Excel and Power BI

Hi @ankitpatir.  Thanks for your response, however the Power BI report is not right.  The query is asking for the MTD values.  Just because there was only one segment on 30th Sep doesn't change the fact that there was $200 worth of sales in the month for segments A and B.  As I said, excel is doing the right thing.

 

As a further test, I created a very simple PBIX file with a very simple model consisting of the date table and an invoice table with the data shown in my original post.  When I create a report against this model, PBI desktop is now doing the right thing.  It is showing MTD values for all 3 segments.  I have analysed the DAX being generated, and it is very different to that being generated against my on premise tabular model (SSAS 2014).  I think the bug might be in the DAX that is generated against the old version of SSAS.

 

Here is the DAX generated using the PBIX model (using the latest DAX features)

 

DEFINE VAR __DS0FilterTable = 
  FILTER(
    KEEPFILTERS(VALUES('Date'[Date])),
    'Date'[Date] = DATE(2016, 9, 30)
  )

EVALUATE
  TOPN(
    502,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('Invoice'[Segment], "IsGrandTotalRowTotal"),
      __DS0FilterTable,
      "MTD_Net_Revenue", 'Invoice'[MTD Net Revenue]
    ),
    [IsGrandTotalRowTotal],
    0,
    'Invoice'[Segment],
    1
  )
ORDER BY
  [IsGrandTotalRowTotal] DESC, 'Invoice'[Segment]

and here is the DAX generated against my SSAS 2014 tabular model:

EVALUATE
  TOPN(
    501,
    CALCULATETABLE(
      ADDCOLUMNS(
        KEEPFILTERS(
          FILTER(
            KEEPFILTERS(VALUES('Invoice'[Segment])),
            NOT(ISBLANK('Invoice'[MTD Net Revenue]))
          )
        ),
        "MTD_Net_Revenue", 'Invoice'[MTD Net Revenue]
      ),
      KEEPFILTERS(
        FILTER(KEEPFILTERS(VALUES('Date'[Date])), 'Date'[Date] = DATE(2016, 9, 30))
      )
    ),
    'Invoice'[Segment],
    1
  )
ORDER BY
  'Invoice'[Segment]
Community Support Team
Community Support Team

Re: Getting different results in Excel and Power BI

Hi @cmn,

 

Has any other issues you faced on these steps?


Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 288 members 2,771 guests
Please welcome our newest community members: