Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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])
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi , @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
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])
@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:
@edhans sorry i uploaded image in a separate reply because it was giving error. Thanks for your help
Hi @adnanarain ,
You can try this
https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991
Regards,
Harsh Nathani
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |