Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculation / Measure

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 - T...
Need Help
  • AilleryO's avatar
    AilleryO
    6 years ago

    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

  • AilleryO's avatar
    AilleryO
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
AilleryO
Icon for Memorable Member rankMemorable Member
6 years ago

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