cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jodes Regular Visitor
Regular Visitor

Data in Table is different than in my Excel spreadsheet

The question is self explanatory via this picture: https://imgur.com/a/ppUGkJ9 

 

Why is there a discrepancy?

 

Excel spreadsheet number of starts: 1,757 (correct)

KPI Indicator starts:                     1,757 (correct)

Table starts:                                 1,747 (wrong)

 

Any ideas? I don't have any filters applied to my Table.

 

This is happening to all my numbers in the Table.

5 REPLIES 5
Jodes Regular Visitor
Regular Visitor

Re: Data in Table is different than in my Excel spreadsheet

Here's the measure for Quarter over Quarter calculations in my table. The other measures are just sums of other columns. Eg. Starts, Spec For Sale.

 

Starts QoQ% =
IF(
ISFILTERED('All Data'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_QUARTER =
CALCULATE(
SUM('All Data'[Starts]),
DATEADD('All Data'[Date].[Date], -1, QUARTER)
)
RETURN
DIVIDE(SUM('All Data'[Starts]) - __PREV_QUARTER, __PREV_QUARTER)
)

 

 

And here's for Year over Year:

 

Starts YoY% =
IF(
ISFILTERED('All Data'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_YEAR =
CALCULATE(
SUM('All Data'[Starts]),
DATEADD('All Data'[Date].[Date], -1, YEAR)
)
RETURN
DIVIDE(SUM('All Data'[Starts]) - __PREV_YEAR, __PREV_YEAR)
)

Community Support Team
Community Support Team

Re: Data in Table is different than in my Excel spreadsheet

Hi @Jodes

 

You may try to check the sample report here. If it is not your case, please share your sample file.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jodes Regular Visitor
Regular Visitor

Re: Data in Table is different than in my Excel spreadsheet

Hi. I've figured out the issue.

 

In my current table I'm using the "Date" field, which I've set to "Date Hierarchy" and thus appears as "Year" and "Quarter" in my table. I used this so that I could calculate the % change YoY and QoQ. This results in my numbers not matching my dataset.

 

However, when I add "Year" and "Quarter" from my dataset into my table, the numbers in my table match my dataset and are correct. But then I cannot use the formula to calculate the % change YoY and QoQ.

 

Can I still create a % change formula without having DATEADD? See my above formula.


Hopefully this makes sense.

Community Support Team
Community Support Team

Re: Data in Table is different than in my Excel spreadsheet

Hi @Jodes

 

You may refer to below post.

 

https://community.powerbi.com/t5/Desktop/Help-with-YOY-DAX-Calculation-with-Filter/td-p/359915

https://community.powerbi.com/t5/Desktop/How-to-show-QoQ-growth/td-p/200813

 

Regards,
Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jodes Regular Visitor
Regular Visitor

Re: Data in Table is different than in my Excel spreadsheet

Hi @v-cherch-msft. I figured out the problem and it was with my data.