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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
naveen73
Helper III
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.

 

naveen73_0-1644615475282.png

 

Product_Static

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

naveen73_1-1644615499292.png

 

 

FX

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

naveen73_2-1644615518816.png

 

 

Sales

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

 

naveen73_3-1644615590061.png

 

 

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

 

naveen73_4-1644615651626.png

 

So far I have only this:

naveen73_5-1644615730232.png

 

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?
 
naveen73_6-1644616213880.png

 

Thanks,

 

Naveen

 
1 ACCEPTED SOLUTION

Hi @naveen73
Here is the sample file with solution https://www.dropbox.com/t/xunNRWT4oOEOU3Me
The report looks like this
Untitled.png
The model looks like this
Untitle.png
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.

View solution in original post

12 REPLIES 12
v-robertq-msft
Community Support
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.

v-robertq-msft
Community Support
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.

v-robertq-msft
Community Support
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.

tamerj1
Super User
Super User

Hi Navin,

Any sample file?

 

Thank you

Yes, how do I attach this?

@naveen73 

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

Hi @naveen73
Here is the sample file with solution https://www.dropbox.com/t/xunNRWT4oOEOU3Me
The report looks like this
Untitled.png
The model looks like this
Untitle.png
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.

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)"?

naveen73_0-1663507468830.png

 

  • 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!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors