Calculation / Measure

[gcharpentier]

Hello. I am new with PW-BI and I am trying to sum the "invoiced amount" by sales rep and by month.  I have 4 tables but doesn't seem to work for sales. I get the targets, but not sales.

• Table 1 - Targets
• Table 2 - Sales
• Table 3 - Accounts
• Table 4 - Date

[AilleryO · Accepted solution]

Hi,

You have a problem of relationship.

Some of your relationship seems to be inactive (those with dash lines not continuous lines).

Right clic on them to activate them.

If you're unable to activate them, maybe you should use
the CROSSFILTER function in your measure to calculate Targets amount.

To give you an example :

Mesu Montant Compte = CALCULATE(
SUM(TabMontant[MontantCompte]); 
CROSSFILTER(
'Client-Compte'[NumCompte];TabMontant[NumCompte]; //Columns to link (relation)
Both)) //Direction of relation

Hope it helps

[V-lianl-msft]

Hi @gcharpentier ,

You can create an unrelated calendar table and use it as slicers and then try DAX like below:

Measure 2 = CALCULATE(
                SUM(Sheet1[value]),
                FILTER(
                    ALLEXCEPT(Sheet1,Sheet1[employee]),
                    YEAR(Sheet1[date])=SELECTEDVALUE('Table 2'[year])&&
                    MONTH(Sheet1[date])=SELECTEDVALUE('Table 2'[month])))

If the problem persists,could you please share sample data or sample pbix?(Please mask any sensitive data before uploading)

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

[gcharpentier]

Thanks, Liang for your comment. I solved the issue with the first reply from another supporter. Now, I have created a new table : Sales Team (this information is not available in my data source, only the sales rep name). In my new table I used this formula to get the sales rep names:

• Sales Team = SUMMARIZECOLUMNS('Accounts Report '[Sales Rep])

But I need a second column [Sales Team] where I need to manually input the sales team name. I was thinking to use this formula:

• Sales Team = IF('Sales Team'[Sales Rep]="John Doe","Sales Team 1", IF('Sales Team'[Sales Rep]="David Smith","Sales Team 2"))

Any other suggestion?

Thanks again, in advance.

G

[AilleryO · Accepted solution]

Hi,

Depends on how many Salesmen you have to deal with.

With a few the IF function will be fine,

if you have more than a few I'll suggest to use SWITCH (link to one example)

If you have a lot, then the best would be a table with sales men and their team, and link this table to your sales fact.

Have a nice day, and do not hesitate if you need more explanation

[AilleryO · Accepted solution]

Hi,

You have a problem of relationship.

Some of your relationship seems to be inactive (those with dash lines not continuous lines).

Right clic on them to activate them.

If you're unable to activate them, maybe you should use
the CROSSFILTER function in your measure to calculate Targets amount.

To give you an example :

Mesu Montant Compte = CALCULATE(
SUM(TabMontant[MontantCompte]); 
CROSSFILTER(
'Client-Compte'[NumCompte];TabMontant[NumCompte]; //Columns to link (relation)
Both)) //Direction of relation

Hope it helps