Solved: Need a DAX measure to replace an Excel function. - Page 2

PaulMac · ‎08-12-2019

Hi

The table in the attached file is a Pivot Table with the exception of the last column (YTD Complaints per 1,000 Transactions) which is a formula that I have outlined above (column G).

The formula basically performs the following calculation:

Add together (Total No of Cases from all months in pivot) Divide by (Total No of Transactions from all months in pivot) x 1000

this formula looks like:

=SUMPRODUCT($C$8:C11)/SUMPRODUCT($D$8:D11)*1000

Simple enough, right?

Now, I need to create a DAX measure that will replace this formula so that I can be used in the existing pivot table and leter on in Power BI. I'm sure this can be done, just not with my current knowledge.

Sample Data file can be foud here.

Please speak slowy and with patience as I am still a noob!

Any and all help would be greatly appreciated.

PaulMc

RobbeVL · ‎08-12-2019

Hi there,

I wish everyone was as clear in describing their needs! 🙂

I think the below will help you.

I simply created the running total for both columns, devided it and *1000.

Calc = 

DIVIDE(
CALCULATE(
	SUM('Table'[Number of Complaints]);
	FILTER(
		ALLSELECTED('Table'[Month]);
		ISONORAFTER('Table'[Month]; MAX('Table'[Month]); DESC)
	)
); CALCULATE(
	SUM('Table'[Number of Key Transactions]);
	FILTER(
		ALLSELECTED('Table'[Month]);
		ISONORAFTER('Table'[Month]; MAX('Table'[Month]); DESC)
	)
)
)*1000

Hope this helps.

View solution in original post

Need a DAX measure to replace an Excel function.

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly