Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey Guys,
I'm looking to do an inner join on a uniqueID from one table to another and then take the sum of a dollar amount from the other table. I want to write this as a DAX measure.
So I have 4 Tables right now like this:
What I want to do is a running inner join on the "ID" on TableA and TableB and sum the "Spent" on TableB , where the running total is 3 months ahead (eg. Jan 2018 sum would be Jan 2018, Feb 2018, Mar 2018, and Apr 2018). So if I plot a a table it would look like this:
I already have a function to do a normal rolling sum, like this:
Roll3Sum = CALCULATE ( SUM ( TableB[Spent] ), FILTER ( ALL ( 'Date' ), 'Date'[Rank] >= MAX ( 'Date'[Rank] ) && 'Date'[Rank] <= MAX ( 'Date'[Rank] ) + 3 ) )
I wanto to perform this but inner join on ID's on the specified month and roll it 3 months ahead like mentioned above.
*EDIT*
here is some sample data, you will see that in the FinalOutput tab, the TotalSpent column is an inner join on the ID'S from tab A and tab B with a 4 month roll including the selected month and the sum of the Spent column from tab B.
Here is the dropbox link:
https://www.dropbox.com/s/mvjzeodkvv5flwi/TestExample.xlsx?dl=0
Solved! Go to Solution.
Hi,
You are welcome. You may download my PBI file from here.
Hope this helps.
Hi,
Share TableA and TableB data (in a format that can be pasted in MS Excel) and show the expected result.
@Ashish_Mathur Hey, I uploaded the sample data onto my original post under *EDIT*. Thank you!
Hi,
In the FinalOutput worksheet, i understand how you got the figures in column C. How did you arrive at the numbers in column D? Show the Excel formula in those cells.
@Ashish_Mathur Hey Ashish, I wrote these example values in Python, but I need to use Power BI for this particular task because I want to be able to dynamically filter visuals.
How have you arrived at the numbers in column D? What logic have you used. Unless i know the logic, i cannot translate that into DAX formulas.
Hey Ashish, so the logic is as follows:
FinalOutput Tab Values:
Date (Column B): Dates for the Values in Table A after grouping their Total Available.
Total Available (Column C) : Total Sum of the Total Available from Table A
TotalSpent (Column D): Sum of Spent from Table B after doing an inner Join on ID from table A and table B and rolling the sum of Spent by 4 months.
For Example in that exact spreadsheet:
If you take row 5 where date = 2019-02-28
Total Available is just the sum of Total Available from tab A where date == "2019-02-28",
which are the IDS = [12,18,16] and Total Available =[41,89,66] with a total of 196.
After getting these ID's inner join table B with a 4 month roll:
Filter Table B with dates ['2019-02-28',"2019-03-31",2019-04-30,"2019-05"31] and ID's from table A,
in this case it would ID = [16,16,18,18] with Spent = [10,38,65,38] which is the TotalSpent in the FinalOutput tab
equal to 151
Ratio: Total Spent / Total Available
Thank you for helping out!
Hi,
You may download my PBI file from here.
Hope this helps.
Hey, Ashish
Thanks so much for this, is possible to return blank values if the date does not have 3 month roll available? So for dates ('2019-03-31', '2019-04-30' and '2019-05-31'), since 3 months in advance do not exist in this dataset.
Thank you!
Hi,
You are welcome. You may download my PBI file from here.
Hope this helps.
@Ashish_Mathur Hey Ashish, appreciate all the help so far, its been very insightful. Just 1 more question, is it possible to have slicers work with this DAX equation? I will upload the same .PBIX file you had, but add 1 more column "SalesPerson" in the Available Table and name the table Available (2).
I want to able to create a slicer (or multiple) that will do what you've done above, but be able to slice and show their Individual data in the Merge1 Summary table.
Thanks!
https://www.dropbox.com/s/kqtrf7kuai326np/Available%20and%20spend%20v1.pbix?dl=0
Hi,
Share the source Excel file.
Hey Ashish,
Here is the excel file:
https://www.dropbox.com/s/ycfjzjoe8ewzj13/TestExample_v1.xlsx?dl=0
As mentioned before, I would like to able to slice on the values in "SalesPerson" and "Position" but have the values in the FinalOutput if there is no slicing.
Thank you so much, I really appreciate this.
Hi,
How is it that in Tab B, there are no Sales Person and Position columns?
Hey Ashish
Yes, that is part of the issue. I need to be able to slice on Table A (SalesPerson and Position) and after slicing, table A would inner join with Table B on ID with a 4 roll month and take the sum of Spent (Table B). You were able to produce a solution in the previous post(s) but without slicing other columns. Is what i'm asking possible in PowerBI?
Hi,
Unless we have those 2 columns in TabB, the end result will not make any sense.
So it's not possible to be able to slice on Tab A (SalesPerson or Position) and then use the solution you had gave me in the previous post?
Hi,
Since those 2 columns are only in TabA, the calculations based on only that Table can be slicer by the two new columns. The calculations on TabB and ratio will not be slicer because these 2 columns are not on TabB.
Hope this clarifies.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |