DAX Measure in Power BI Gives Different Result in Excel

[PaulMac]

Hello All

I have been asked to transfer a report from Excel to Power BI.

All the queries, connections and relationships have been mirrored with the Excel report.

I wanted to use the same DAX measures and copied the 2 that I required from the Excel report.

One of these DAX measures that gives a value for the the volume of complaints received per 1,000 transactions processed, is absolutely fine and the value matches with the Excel report. Perfect.

However, the second DAX that looks for a YTD value DOES NOT produce the same value as the Excel report as can be seen in the screen grabs below (Excel report is the first image with the second from Power BI).

screen grab from Excel report      Screen grab from Power BI report

Here is the code that is giving me the erroneous value:

YTD Complaints per 1,000 Transactions = DIVIDE(
CALCULATE(
	SUM('ComplaintsVsTransactions'[Number of Complaints]),
	FILTER(
		ALLSELECTED('ComplaintsVsTransactions'[Month]),
		ISONORAFTER('ComplaintsVsTransactions'[Month], MAX('ComplaintsVsTransactions'[Month]), DESC)
	)
), CALCULATE(
	SUM('ComplaintsVsTransactions'[Number of Key Transactions]),
	FILTER(
		ALLSELECTED('ComplaintsVsTransactions'[Month]),
		ISONORAFTER('ComplaintsVsTransactions'[Month], MAX('ComplaintsVsTransactions'[Month]), DESC)
	)
)
)*1000

Due to the sensitivity around the data I am unable to produce any sample file for you to look at.

Please let me know if there is something obvious that I am doing wrong. And if anyone can give an explaination as to why this may happen that would most appreciated. 🙂

Paul

[v-alq-msft]

Hi, @PaulMac 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft · Accepted solution]

Hi, @PaulMac 

Based on your description, I created data to reproduce your scenario.

ComplaintsVsTransactions:

you may create a calculated table and create a relationship between two tables as follows.

DateTable = CALENDARAUTO()

Then you can create a measure as below.

YTD Complaints per 1,000 Transactions = 
SUMX(
    'ComplaintsVsTransactions',
    DIVIDE(
        CALCULATE(
            SUM('ComplaintsVsTransactions'[Number of Complaints]),
            DATESYTD(DateTable[Date])
        ),
        CALCULATE(
            SUM('ComplaintsVsTransactions'[Number of Key Transactions]),
            DATESYTD(DateTable[Date])
        )
    )

)

Result:

If I misunderstand your thought, please show me your expected output. Do mask sensitive data before uploading. I am glad to solve the problem for you.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @PaulMac ,

Believe that your issue is related with the ONORAFTER setup. How is the month column presented in numbers? do you have information for more than 1 year on your dataset? This can impact on the calculations you are making.

Do you have any date column on your data? If yes I would use that column to make the YTD calculation using a FILTER or a TOTALYTD formula.

Regards,

MFelix

[PaulMac]

@MFelix Thanks for the reply. 

---

@MFelix wrote:

do you have information for more than 1 year on your dataset? This can impact on the calculations you are making.

---

That was it! After doing a manual filter on the data to only show FYTD months (UK: Apr 19 - Dec 19), I get the correct values.

---

Do you have any date column on your data? If yes I would use that column to make the YTD calculation using a FILTER or a TOTALYTD formula.

---

I do have a date column, could you advise how I can go about making a YTD calculation using a FILTER or a TOTALYTD formula, or point me in the right direction.

[MFelix]

Hi @PaulMac ,

Simplifying things you can make a measure similar to this:

=TOTALYTD(SUM('ComplaintsVsTransactions'[Number of Complaints]),Table[Date])  

Regards,

MFelix 

[PaulMac]

@MFelix 

Cool, have created the TotalYTD measure but am unsure what to do with it now. Where should I put this new DAX Measure?

[MFelix]

Hi @PaulMac ,

You should use it in a syntax similar to the one below.

YTD Complaints per 1,000 Transactions =
TOTALYTD (
    DIVIDE (
        SUM ( 'ComplaintsVsTransactions'[Number of Complaints] ),
        SUM ( 'ComplaintsVsTransactions'[Number of Key Transactions] )
    ) * 1000,
    Table[Date]
)