Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
When a date is selected in B3, the following happens:
So far I have only this:
It shows only that day.
Total_Sales is a measure (and the only measure I have so far,
Thanks,
Naveen
Solved! Go to Solution.
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.
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.
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.
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.
Hi Navin,
Any sample file?
Thank you
Yes, how do I attach this?
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
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.
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:
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
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |