Forum Discussion

PaulMac's avatar
PaulMac
Helper IV
7 years ago
Solved

Need a DAX measure to replace an Excel function.

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

Need Help
Tutorial Requests
  • RobbeVL's avatar
    RobbeVL
    7 years ago

    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.

16 Replies

  • RobbeVL's avatar
    RobbeVL
    Impactful Individual
    7 years ago

    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.

    • PaulMac's avatar
      PaulMac
      Helper IV
      7 years ago

      Hi RobbeVL 

      Many thanks for the swift reply. Perhaps I am either doing something wrong or I haved missed something but that measure you wrote, give me the same output as a previous measure I wrote of:

      Complaints Per 1,000 Transactions = CALCULATE(SUMX(All_Complaints,[DAXComplaintCount])/SUMX(Total_key_Transactions,Total_key_Transactions[Total Key Transactions])*1000)

      See attached image for more details.

       I need any measure that would output the below circled in RED:

      Also, can I confirm that where you have used a semicolon ; shouldn't these have just been commas , as Power BI doesn't accept the semicolon in a DAX measure. Or am I mistaken?

      • RobbeVL's avatar
        RobbeVL
        Impactful Individual
        7 years ago

        Hi,

         

        Strange,

         

        Please have a look here:

         

        I seem to get the correct result? ...
        make sure you adjusted my measure correctly.

         

        robbe