Trying to ma the difference between 2 columns in different tables in a measure

[PeterVachon]

Hi, 

I have 2 tables 

1) Sales tables (daily)

2) Forecast tables (monthly)

(Both are link to my look up tables :  product, customer, calendar)

I am trying to have a measure that would help my compare the total per different caterogies (1st step is the SalesRep) 

But every measure i've tried only calculate totals and i cannot use my different filter

Ex :  Want to compare total of sales at the end of may per REP vs their forecast.   Then i'll want to compare client by client... etc... so it has to be dynamic based on the date. 

Any tips on how to write this ?  I am really new with Dax and struggling a bit here. 

[amitchandak · Accepted solution]

@PeterVachon ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Need to try with data

[v-yangliu-msft · Accepted solution]

Hi  @PeterVachon ,

This is the data I created:

Sales tables:

Forecast tables:

look up tables:

Here are the steps you can follow：

1. Create measure.

alltoal =
var _select=
SELECTEDVALUE('look up table'[date])
return
CALCULATE(SUM('Sales tables'[amount]),FILTER(ALL('Sales tables'),'Sales tables'[Customer]=MAX('Sales tables'[Customer])&&'Sales tables'[Month]=MONTH(_select)))

Forecastamount =
var _select=SELECTEDVALUE('look up table'[date])
return
CALCULATE(SUM('Forecast tables'[Amount]),FILTER(ALL('Forecast tables'),'Forecast tables'[monthly]=MONTH(_select)&&'Forecast tables'[Customer]=MAX('Forecast tables'[Customer])))

Flag =
IF(MAX('Sales tables'[date]) in SELECTCOLUMNS('look up table',"1",[date]),1,0)

Flag1 =
IF(MAX('Forecast tables'[monthly]) in SELECTCOLUMNS('look up table',"1",MONTH('look up table'[date])) ,1,0)

2. Put [Flag] and [Flag1] into the Filter of the corresponding visual object respectively, is=1, apply filter.

3. Put [date] of the look up table table into the slicer, according to the selected date, to display the corresponding total sales and monthly forecast value, and compare

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PeterVachon]

To better explain,  I've tried splitting up my formual to see what is going on.

1st part of my formula isn't working correctly it returns the TOTAL sales YTD and doesn't spread per REP as i would like it to in my matrix, 

Measure = CALCULATE(sumx(FILTER('Sales','Sales'[invoicedt] >= 2021-01-01),'Sales'[NET]))

It's probably not the right operator... calculatetable also deliver the sames results. 

[amitchandak]

@PeterVachon , Not very clear on the issue. Becuase if you have calendar table, joined to both tables, then you can compare actual and forecast values monthly 

if sales rep is not a common dimesion, then you can not compare that.

If handeling date is an issue refer

Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

[PeterVachon]

What i am trying to do is write a measure that with take my Total Sales -  Forecast and be able to spread it out per REP or customer.  Depending on how I present my Matrix/Table.   If i only use a quick measure it doesn't work and doesn't spread correctly. 

But, I keep getting only the TOTAL.

My filter regardings the dates is working well... no worry. 

BTW : Thx for your link this will come up handy real soon 😉 ! 

[amitchandak · Accepted solution]

@PeterVachon ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Need to try with data