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.
Hey guys,
I am struggling with this problem.
In my dataset I have one table with the product family, date (of sale), month and code:
Family Date Month Code
A 4/12/20 APR 10
B 2/12/20 FEB 20
C 3/12/20 MAR 10
...
From this table I made a matrix with Family on the rows, Month on the lines and Count of date on the values
The other table from my dataset has the forecast of sales wich contains the following columns: family, value and month
Family Value Month
A 12 APR
A 7 MAR
B 7 MAR
...
From this one I made another matrix showing our forecast for each family in each month.
Now, I need to build another matrix that shows my sales by family and month subtracted from the forecast, showing the difference of Forecast and Sales, but in this one I need to apply one filter (that is code=10).
I was going for a quick measure and a measure but couldn't do it properly, can anyone help me?
Thks!
Solved! Go to Solution.
@Anonymous What about a measure like:
Measure =
VAR __Month = MAX('Forecast'[Month])
VAR __Family = MAX('Forecast'[Family])
VAR __Forecast = MAX('Forecast',[Value])
VAR __Sales = COUNTROWS(FILTER('Sales',[Month]=__Month && [Family]=__Family))
RETURN
__Forecast - __Sales
This makes some assumptions about your visual.
I am trying to upload my pbi file but couldn't find the right way to do it!
@Anonymous - Seems doable but missing a value column of sales in first table? To share a PBIX, put it on OneDrive or Box or something and share a public link to it.
@Greg_Deckler Actually, it is not missing anything because it doesn't matter actually the value of the sales, just the amount of sales for each family, the column "vsalue" on the second table goes for the number of sales.
Here it goes the link: https://drive.google.com/file/d/15-GewmUTetS3-klGVHzabqKl4tUkZnmt/view?usp=sharing
Hi,
That link does not work. Show the expected result very clearly.
@Anonymous What about a measure like:
Measure =
VAR __Month = MAX('Forecast'[Month])
VAR __Family = MAX('Forecast'[Family])
VAR __Forecast = MAX('Forecast',[Value])
VAR __Sales = COUNTROWS(FILTER('Sales',[Month]=__Month && [Family]=__Family))
RETURN
__Forecast - __Sales
This makes some assumptions about your visual.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |