Calculate Measure that is duplicated on more than one row

[ritasweidy]

Dear Sir, 

how can we calculate target of the (Salesman , Month(date),Year(date) , Type) if the target is duplicated on more than one row in order to get the daily sales

the Target of e1 for Type New and Month January 2017 is 46000 , how can i get 46000 when i group by Salesman , Type, Month and Year(date)

date	salesman	Type	Total	Target
02/01/2017	e1	New	134.00	46,000.00
02/01/2017	e2	New	1,022.73	19,000.00
02/01/2017	e3	Renewal	515.00	0.00
03/01/2017	e1	New	208.15	46,000.00
03/01/2017	e2	New	259.09	19,000.00
03/01/2017	e3	New	415.00	35,000.00
03/01/2017	e3	Renewal	573.00	0.00
04/01/2017	e1	New	2,152.00	46,000.00
04/01/2017	e2	New	259.09	19,000.00
05/01/2017	e1	New	2,578.00	46,000.00
05/01/2017	e2	New	972.73	19,000.00
05/01/2017	e3	New	625.00	35,000.00
05/01/2017	e3	Renewal	770.00	0.00
06/01/2017	e1	New	2,750.50	46,000.00
06/01/2017	e2	New	668.18	19,000.00
06/01/2017	e3	New	115.00	35,000.00
08/02/2017	e3	New	250	18498
08/02/2017	e3	Renewal	125.45	0
10/02/2017	e3	New	735	18498
10/02/2017	e3	Renewal	946.96	0
11/02/2017	e3	New	1700	18498
11/02/2017	e3	Renewal	2365	0
13/02/2017	e1	New	1998.5	54991
13/02/2017	e2	New	140.91	20000
13/02/2017	e3	New	894.5	18498
13/02/2017	e3	Renewal	2150	0
14/02/2017	e1	New	1997	54991
14/02/2017	e2	New	77.27	20000
14/02/2017	e3	New	484.55	18498
14/02/2017	e3	Renewal	820	0
15/02/2017	e1	New	956.75	54991
15/02/2017	e2	New	-259.09	20000

[themistoklis]

@ritasweidy

Best to create a new table with months and their targets:

New_Table = SUMMARIZE(Table1,Table1[Month],Table1[Target])

Create a relationship between this table and the main one that you have using month column as key.

Then get the target values from the new table

Measure = AVERAGE(New_Table[Target])

[affan · Accepted solution]

Hi @ritasweidy,

You can use the following DAX to create a measure for the desired result.

MonthlyTarget = var _montarg= AVERAGEX(SUMMARIZE(Table1,[salesman],[date].[Month],Table1[Type],"AvgMonthTarget",AVERAGE(Table1[Target])),[AvgMonthTarget])
return 
_montarg

I have got the below result from this

You can also download the pbix here

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

[ritasweidy]

Thanks a lot , it worked perfectly

[v-lili6-msft]

hi, @ritasweidy

You may try to use AVERAGE Function to create a measure for Target and then use it for other calculation

Target measure = CALCULATE(AVERAGE('Table'[Target]))

Best Regards,

Lin

[ritasweidy]

Dear Sir, 

this will work if only i want to work on one month , but if i have a filter in my dashboard and i want to work with 2 months or more , if i use the average then the sum of the target measure will be calcuated as average not as a sum 

is there a way to fix this?

Regards

[v-lili6-msft]

hi, @ritasweidy

This will lead to a total problem, very normal, and here is a post for you refer to

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Best Regards,

Lin