Convert Excel Formula sum product to power bi

[adnanarain]

Dear All,

How can i convert following excel formula into power bi:

=IFERROR(ROUND(IF(SUMPRODUCT(J4:AN4,BU4:CY4)*21/((AO4/31)*23.25)-1<2,SUMPRODUCT(J4:AN4,BU4:CY4)*21,(AO4/31)*23.25),0),0)

J4:AN4 = daily sale 

BU4:CY4 = Average of daily sale

[edhans]

We'd need do see some actual data, and I suspect you need to do some unpivoting. First, here is a basic measure:

COALESCE(
	ROUND(
		IF(
			SUMX(
				Table,
				Table[ColumnJ] * Table[ColumnAN] * 21 / ((varWhatverisAO4/31) * 23.25 - 1
				) < 2,
			SUMX(
				Table,
				Table[ColumnJ] * Table[ColumnAN] * 21
				),
			(varWhatverisAO4/31) * 23.25
			),
			0
		),
		0
	)

SUMX() will take do what a SUMPRODUCT() will, but not over a range of columns like you have. SUMPRODUCT is doing J4 * BU4, then J5*BU5, etc. then moves to K4*BV4 and so on. I think those columns J4:An4 and BU4:CY4 should be normalized into rows, not columns.

You cannot refer to a single cell like AO4 in DAX either as there are no cell addresses, so you'd need to tease that out using a MAX or MAXX function with filter criteria.

Again, we'd need to see some real data.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[adnanarain]

@edhans  AO4 is simple sum of all the sales quanity

[v-easonf-msft]

Hi , @adnanarain 

Do you have resolved it? If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

[adnanarain · Accepted solution]

Hi Yes i have resolved it. I have added following measure:

% of Total = DIVIDE (
[Total Sales Quantity],
CALCULATE ( [Total Sales Quantity], ALLSELECTED('Date'[Date])))

Sum Product 2 = [Total Sales Quantity] * [% of Total]

Sum Product 3 = Sumx(SUMMARIZE('Date','Date'[Date], "Top", [Sum Product 2]),[Top])

[adnanarain]

@edhans  Thank you so much for the reply:

Below is the actual data

SKU	Total Sales Quantity	Date
1	6	06/27/20
1	11	06/28/20
1	21	06/29/20
1	28	06/30/20
1	25	07/01/20
1	15	07/02/20
1	14	07/03/20
1	14	07/04/20
1	13	07/05/20
1	11	07/06/20
1	8	07/07/20
1	1	07/10/20

this is how I am doing in excel:

[adnanarain]

@edhans sorry i uploaded image in a separate reply because it was giving error. Thanks for your help

[adnanarain]

[harshnathani]

Hi @adnanarain ,

You can try this

https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991

Regards,

Harsh Nathani