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,

 

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