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

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

    • PaulMac's avatar
      PaulMac
      Helper IV
      7 years ago

      Hi RobbeVL 

      Your code:

      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

      My code:

      Calc2 = 

DIVIDE(
CALCULATE
    (SUM('All_Complaints'[count]),
    FILTER(
        ALLSELECTED('Date_Table'[Month]),
        ISONORAFTER(Date_Table[Month],MAX(Date_Table[Month]), DESC)
        )
), CALCULATE(
    SUM('Total_key_Transactions'[Total Key Transactions]),
    FILTER(
        ALLSELECTED(Date_Table[Month]),
        ISONORAFTER(Date_Table[Month],MAX(Date_Table[Month]),DESC)
    )
)
)*1000

      My complaints figures [count] are daily volumes, whereas yours [Number of Complaints] are monthly volumes. Unsure if that is causing the problem. If so, how can i get arounfd this?

      • RobbeVL's avatar
        RobbeVL
        Impactful Individual
        7 years ago

        Indeed!
        Change month to the lowest date hierarchy and this should be fine.
        every month can be "date"

    • PaulMac's avatar
      PaulMac
      Helper IV
      7 years ago

      Also, how is your Date_Table[Month] column formatted? Is this just the month number i.e. 1-12 (Jan-Dec)

      Cheers

      Paul

      • RobbeVL's avatar
        RobbeVL
        Impactful Individual
        7 years ago

        This cannot be a text value.

        It needs to be a numeric or date value