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

This cannot be a text value.

It needs to be a numeric or date value

PaulMac's avatar
PaulMac
Helper IV
7 years ago
RobbeVL wrote:

This cannot be a text value.

It needs to be a numeric or date value

It is a numerical value RobbeVL

 

  • PaulMac's avatar
    PaulMac
    Helper IV
    7 years ago

    OK I have done something very wrong here. Why is is this so difficult!!

     

    My Measure:

    Calc2 = 

DIVIDE(
CALCULATE
    (SUM('Monthly Complaints Received'[MonthVolume]),
    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

    Your measure:

    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

    Our measures are basically a mirror of each other. So why am I getting a different outcome to you?

    Are you able to share your sample file so I may see how you acheived this in mintues what is currently taking me hours.

    :smileysad:

    • RobbeVL's avatar
      RobbeVL
      Impactful Individual
      7 years ago

      try this:

      (relace month by your date value in that table)

      Calc2 = 

DIVIDE(
CALCULATE
    (SUM('Monthly Complaints Received'[MonthVolume]),
    FILTER(
        ALLSELECTED('Monthly Complaints Received'[Month]),
        ISONORAFTER('Monthly Complaints Received'[Month],MAX('Monthly Complaints Received'[Month]), DESC)
        )
), CALCULATE(
    SUM('Total_key_Transactions'[Total Key Transactions]),
    FILTER(
        ALLSELECTED('Monthly Complaints Received'[Month]),
        ISONORAFTER('Monthly Complaints Received'[Month],MAX('Monthly Complaints Received'[Month]),DESC)
    )
)
)*1000

       Looks like I already deleted the sample file.
      If its doesnt work now, I'll remake it :)

      • PaulMac's avatar
        PaulMac
        Helper IV
        7 years ago

        RobbeVL 

        Nope that last recommendation made absolutely no difference.

        Please could rebuild and submit a sample file so I can see where I am going wrong?

        Thanks

        PaulMc