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.

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

    Many , many thanksRobbeVL . As the saying goes, not all heroes wear capes!

    I managed to correct my issue using your sample file.

    All the best.

    PaulMc

     

     

  • 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

  • RobbeVL's avatar
    RobbeVL
    Impactful Individual
    7 years ago

    Welcome!

    Please mark as resolved! :)