Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cmn
Helper III
Helper III

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
Community Champion
Community Champion

@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.

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]

Hi @cmn,

 

Has any other issues you faced on these steps?


Regards,

Xiaoxin Sheng

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.