Make a matrix showing the difference between a value and count

[Anonymous]

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]

I am trying to upload my pbi file but couldn't find the right way to do it!

[Greg_Deckler]

@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.

[Anonymous]

@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

[Ashish_Mathur]

Hi,

That link does not work.  Show the expected result very clearly.

[Greg_Deckler · Accepted 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.

[Anonymous]

@Greg_Deckler 

Yep, that's exactly it man!
Thanks a lot for your help!