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.
I am not sure exactly how to word this question so apologies if this has been answered previously...
Essentially I want to build out a list based on dates, sites and categories. From here I want to merge my sales data, that way I will have a full list of dates, sites and categories regardless of wheteher sales occurred.
As it stands I only have a row in my sales table on the dates sales were registered.
As a simple example this is what I would like to see, I want to build the first 3 columns first with the rows in red the result of this table:
Date | Site | Category | Sales |
01/01/2018 | Site 1 | Food | 1000 |
01/01/2018 | Site 1 | Drinks | 1000 |
01/01/2018 | Site 2 | Food | 1000 |
01/01/2018 | Site 2 | Drinks | |
02/01/2018 | Site 1 | Food | |
02/01/2018 | Site 1 | Drinks | 1000 |
02/01/2018 | Site 2 | Food | 1000 |
02/01/2018 | Site 2 | Drinks | 1000 |
03/01/2018 | Site 1 | Food | |
03/01/2018 | Site 1 | Drinks | 1000 |
03/01/2018 | Site 2 | Food | 1000 |
03/01/2018 | Site 2 | Drinks | 1000 |
Hopefully this makes sense, any help greatly appreciated as always!
Thanks,
Kevin
Solved! Go to Solution.
Hi @Anonymous,
As per my understanding from your question. I have created a sample file which you can download from here.
I have understood that you are having two tables first is your dates table and the second is the transactions table. To acheive your result I have created a custom table with cross joined values from both of these tables.
I used the following DAX to created this custom table.
ResultTable = CROSSJOIN(DateTable,SUMMARIZE(Table1,Table1[Category],Table1[Site]))
You can see the below illustration to understand the results and the attached power bi file.
The lookup column I have created is with the following DAX.
SalesAmount = LOOKUPVALUE(Table1[Sales],Table1[Category],ResultTable[Category],Table1[Dates],ResultTable[Dates],Table1[Site],ResultTable[Site])
This looksup where all three values, date, category and site are same.
The date table I have is having dates within Jan 2018.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
@Anonymous Please try this as a "New Table"
Test156Out = VAR _MinDate = MIN(Test156Data[Date]) VAR _MaxDate = MAX(Test156Data[Date]) VAR _DateGen = CALENDAR(_MinDate,_MaxDate) VAR _Site = VALUES(Test156Data[Site]) VAR _Category = VALUES(Test156Data[Category]) RETURN CROSSJOIN(CROSSJOIN(_DateGen,_Site),_Category)
Proud to be a PBI Community Champion
Hi @Anonymous,
As per my understanding from your question. I have created a sample file which you can download from here.
I have understood that you are having two tables first is your dates table and the second is the transactions table. To acheive your result I have created a custom table with cross joined values from both of these tables.
I used the following DAX to created this custom table.
ResultTable = CROSSJOIN(DateTable,SUMMARIZE(Table1,Table1[Category],Table1[Site]))
You can see the below illustration to understand the results and the attached power bi file.
The lookup column I have created is with the following DAX.
SalesAmount = LOOKUPVALUE(Table1[Sales],Table1[Category],ResultTable[Category],Table1[Dates],ResultTable[Dates],Table1[Site],ResultTable[Site])
This looksup where all three values, date, category and site are same.
The date table I have is having dates within Jan 2018.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
Perfect, thank you very much!
Hi @Anonymous,
I'm a little confused about your requirement.
Is the data you shared your data sample? If it is, could you share your desired output so that I can understand your scenario better and help further on it?
Best Regards,
Cherry
Hi Cherry, the table I shared is my desired final outcome.
The table I am trying to create is the first three columns. My acutal data is the same table minus the rows in red. So when I merge on data/site/category I also get an entry for days with no sales,
The problem is I have over 1,000 sites and 25+ categories so manually creating the first three columns isn't feasible.
Hopefully that makes a bit more sense, I have been struggling to articulate this question for a while!
I have included the actual and target data below
Thanks,
Kevin
What I want to make
Date | Site | Category |
01/01/2018 | Site 1 | Food |
01/01/2018 | Site 1 | Drinks |
01/01/2018 | Site 2 | Food |
01/01/2018 | Site 2 | Drinks |
02/01/2018 | Site 1 | Food |
02/01/2018 | Site 1 | Drinks |
02/01/2018 | Site 2 | Food |
02/01/2018 | Site 2 | Drinks |
03/01/2018 | Site 1 | Food |
03/01/2018 | Site 1 | Drinks |
03/01/2018 | Site 2 | Food |
03/01/2018 | Site 2 | Drinks |
My Actual Data
Date | Site | Category | Sales |
01/01/2018 | Site 1 | Food | 1000 |
01/01/2018 | Site 1 | Drinks | 1000 |
01/01/2018 | Site 2 | Food | 1000 |
02/01/2018 | Site 1 | Drinks | 1000 |
02/01/2018 | Site 2 | Food | 1000 |
02/01/2018 | Site 2 | Drinks | 1000 |
03/01/2018 | Site 1 | Drinks | 1000 |
03/01/2018 | Site 2 | Food | 1000 |
03/01/2018 | Site 2 | Drinks | 1000 |
@Anonymous Please try this as a "New Table"
Test156Out = VAR _MinDate = MIN(Test156Data[Date]) VAR _MaxDate = MAX(Test156Data[Date]) VAR _DateGen = CALENDAR(_MinDate,_MaxDate) VAR _Site = VALUES(Test156Data[Site]) VAR _Category = VALUES(Test156Data[Category]) RETURN CROSSJOIN(CROSSJOIN(_DateGen,_Site),_Category)
Proud to be a PBI Community Champion
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 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |