Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper III

## sumproduct of a sumif

Hi all,

I have a dataset and want to perform some calculations with a measure. To this end I have changed the AdventureWorks dataset (publicly available) to make my problem more understandable.

I have the following tables:

Product_Price

This table lists the price of a product at a specific date. I made a unique column, which consists of the product key and the date.

Product_Static

This table lists the details of a product key: name of the product and the color.

FX

For each day and for each currency, the conversion rate.

Sales

For each product (ProductKey) sold, the date and quantity. Column B makes the line unique: =C2&E2.

What I have so far in Excel is a prototype see screenshot
- in cell E3 we have the formula =UNIQUE(Sales!C2:C41,FALSE,FALSE). This populated the column with all the products that are sold

• in cell B3 I have a dropdown with the date.

When a date is selected in B3, the following happens:

• Column F: =VLOOKUP(\$E3,Product_static!\$A:D,2,FALSE) The product name of Productkey in column E is displayed.
• Column G: =VLOOKUP(\$E3,Product_static!\$A:E,3,FALSE) The color of Productkey in column E is displayed.
• Column H: =SUMIFS(Sales!D:D,Sales!C:C,E3,Sales!E:E,"<="&\$B\$3). The number of products of Productkey that are sold up until the day (and included that day) selected in cell B3.
• Column I =VLOOKUP(E3&\$B\$3,Product_Price!B:E,4,FALSE): the price of ProductKey for that day is selected.
• Column J =VLOOKUP(E3,Product_Price!D:F,3,FALSE): the currency for the Productkey is displayed
• Column K =I3*H3: the sales amount in the currency is calculated.
• Column L: =VLOOKUP(J3&\$B\$3,FX!A:D,4,FALSE)*K3: the sales in GBP is calculated

So far I have only this:

It shows only that day.

Total_Sales is a measure (and the only measure I have so far,

total_sales = SUMx(Sales,Sales[Quantity]*RELATED(ProductPrice[Price]))
Also, the FX table does not connect automatically to any of the other tables. Why is that?

Thanks,

Naveen

1 ACCEPTED SOLUTION
Super User

Hi @naveen73
Here is the sample file with solution https://www.dropbox.com/t/xunNRWT4oOEOU3Me
The report looks like this

The model looks like this

Measures are as follows

``````Product Name =
IF (
HASONEVALUE ( Product_static[ProductKey] ),
VALUES ( Product_static[Product] )
)``````
``````Product Color =
IF (
HASONEVALUE ( Product_static[ProductKey] ),
VALUES (Product_static[Color] )
)``````

``````Inventory =
VAR CurrentDate =
MAX (Sales[Date] )
VAR FilteredTable =
CALCULATETABLE (
Sales,
Sales[Date] <= CurrentDate
)
VAR Result =
SUMX (
FilteredTable,
Sales[Quantity]
)
RETURN
IF (
HASONEVALUE ( Product_static[ProductKey] ),
Result
)``````
``````Unit Price =
VAR Price =
CALCULATE (
VALUES ( ProductPrice[Price] ),
CROSSFILTER ( Sales[Date],'Date'[Date], Both )
)
VAR Result =
IF (
HASONEVALUE ( Product_static[ProductKey] ),
Price
)
RETURN
Result ``````
``````Product Currency =
IF (
HASONEVALUE ( Product_static[ProductKey] ),
VALUES ( ProductPrice[Currency] )
)``````
``Amount In FX = [Inventory] * [Unit Price]``
``````Amount In GBP =
VAR FXRate =
CALCULATE (
VALUES ( FX[Rate] ),
CROSSFILTER ( Sales[Date],'Date'[Date], Both ),
USERELATIONSHIP ( ProductPrice[Currency], FX[Currency] )
)
VAR Result =
IF (
HASONEVALUE ( Product_static[ProductKey] ),
[Amount In FX] * FXRate
)
RETURN
Result``````

Please let me know if this satisfies your requirement. If so please consider marking my reply as accepted solution.

12 REPLIES 12
Community Support

Hi,

Have you followed the pbix file posted by tamerj1to find the solution to your problem?

If so, would you like to mark his reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi,

Have you followed the pbix file posted by tamerj1to find the solution to your problem?

If so, would you like to mark his reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi,

According to your description, I can roughly understand what you want to get. Would you like to post some sample data in table form or pbix file(without sensitive data) and your expected result(like the chart you want to get and the correct measure or column value based on your sample data)?

Thanks very much!

How to Get Your Question Answered Quickly

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

Hi Navin,

Any sample file?

Thank you

Helper III

Yes, how do I attach this?

Super User

Upload to WeShare, OneDive, DropBox or any other cloud service and share the link

Helper III
Super User

Hi @naveen73
Here is the sample file with solution https://www.dropbox.com/t/xunNRWT4oOEOU3Me
The report looks like this

The model looks like this

Measures are as follows

``````Product Name =
IF (
HASONEVALUE ( Product_static[ProductKey] ),
VALUES ( Product_static[Product] )
)``````
``````Product Color =
IF (
HASONEVALUE ( Product_static[ProductKey] ),
VALUES (Product_static[Color] )
)``````

``````Inventory =
VAR CurrentDate =
MAX (Sales[Date] )
VAR FilteredTable =
CALCULATETABLE (
Sales,
Sales[Date] <= CurrentDate
)
VAR Result =
SUMX (
FilteredTable,
Sales[Quantity]
)
RETURN
IF (
HASONEVALUE ( Product_static[ProductKey] ),
Result
)``````
``````Unit Price =
VAR Price =
CALCULATE (
VALUES ( ProductPrice[Price] ),
CROSSFILTER ( Sales[Date],'Date'[Date], Both )
)
VAR Result =
IF (
HASONEVALUE ( Product_static[ProductKey] ),
Price
)
RETURN
Result ``````
``````Product Currency =
IF (
HASONEVALUE ( Product_static[ProductKey] ),
VALUES ( ProductPrice[Currency] )
)``````
``Amount In FX = [Inventory] * [Unit Price]``
``````Amount In GBP =
VAR FXRate =
CALCULATE (
VALUES ( FX[Rate] ),
CROSSFILTER ( Sales[Date],'Date'[Date], Both ),
USERELATIONSHIP ( ProductPrice[Currency], FX[Currency] )
)
VAR Result =
IF (
HASONEVALUE ( Product_static[ProductKey] ),
[Amount In FX] * FXRate
)
RETURN
Result``````

Please let me know if this satisfies your requirement. If so please consider marking my reply as accepted solution.

Helper III

Hi @tamerj1 ,

First, let me thank you for your contribution. It is much appreciated. Just to let you know that the numbers ARE correct. Just to clarify:

• The functionality I was looking for, was to have the total sum whenever I click on a date. Currently, the screenshot shows the total sum per date for each of the products, means the downloaded PBIX is doing its job to a large extent. In the downloaded PBIX, I included a filter on the date and click on one of the dates. In the card that I had, when clicking on the 7 of July, I would expect the sum of £ 1,202,081.88. Is that possible? Why does "Measure_inserted" giving a "(blank)"?

• You, and all the rest, have helped me put this in Power BI. However, I wanted to do this in DAX Excel. I thought this must be easier in DAX in Excel? But when I tried to make relationship, it says that it does not support many-to-many relationships. Is it not strange that I cannot build it in Excel with DAX but I am able to do this in plain Excel.

I am not just looking for an answer but also trying to learn the thinking process. I see that you have used a lot of VAR, which I can use in other DAX formulas. Thanks for that!

Helper III

Hi @tamerj1,

Thanks so much for this. I am able to download the file. It says that the link expired. Please could you renew the link.

Also, I see in the Model view that you have columns like Fiscal Month in Quarter Number.

Thanks so much,

Nav

Super User
Helper III

Hi,

@tamerj1 Really appreciate your time and efforts in this, however, the number that are calucated do not look correct. I just check the number for the 2 of July with my spreadsheet

cheers,

Nav

## Helpful resources

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors