Need a DAX measure to replace an Excel function.

[PaulMac]

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 formula basically performs the following calculation:

Add together (Total No of Cases from all months in pivot) Divide by (Total No of Transactions from all months in pivot) x 1000

this formula looks like:

=SUMPRODUCT($C$8:C11)/SUMPRODUCT($D$8:D11)*1000

Simple enough, right?

Now, I need to create a DAX measure that will replace this formula so that I can be used in the existing pivot table and leter on in Power BI. I'm sure this can be done, just not with my current knowledge.

Sample Data file can be foud here.

Please speak slowy and with patience as I am still a noob!

Any and all help would be greatly appreciated.

PaulMc

[RobbeVL · Accepted solution]

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]

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]

Hi,

Strange,

Please have a look here:

I seem to get the correct result? ...
make sure you adjusted my measure correctly.

robbe

[PaulMac]

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

Cheers

Paul

[RobbeVL]

This cannot be a text value.

It needs to be a numeric or date value

[PaulMac]

---

@RobbeVL wrote:

This cannot be a text value.

It needs to be a numeric or date value

---

It is a numerical value RobbeVL

[PaulMac]

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.

[RobbeVL]

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]

@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]

Here you go

https://drive.google.com/open?id=1v9ba_6hnUGYNv8P2B61UIcGy3rDYw6_X 

Also use the date in your FACT table, NOT from a dimension. That might help the problem.

[PaulMac]

Many , many thanks@RobbeVL . As the saying goes, not all heroes wear capes!

I managed to correct my issue using your sample file.

All the best.

PaulMc

[RobbeVL]

Welcome!

Please mark as resolved! 🙂

[PaulMac]

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]

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

[PaulMac]

---

@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]

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.