Forum Discussion
Make a matrix showing the difference between a value and count
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!
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 - __SalesThis makes some assumptions about your visual.
6 Replies
- Greg_Deckler
Community Champion
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