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
RobbeVL wrote:

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


"Change month to the lowest date hierarchy" I am unsure how to do this. Any chance of some assitance?

RobbeVL's avatar
RobbeVL
Impactful Individual
7 years ago

Make sure your relationships are defined corretly.
Another way to fix your problem, will be to use the datekey in your FACT Table.

so use DATE in the measure as the datefield in the same table.