## Desktop

Frequent Visitor
Posts: 12
Registered: ‎06-28-2018

# Calculate Measure that is duplicated on more than one row

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

Accepted Solutions
Established Member
Posts: 201
Registered: ‎07-12-2017

## Re: Calculate Measure that is duplicated on more than one row

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

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

Regards,

Affan

All Replies
Community Support Team
Posts: 1,317
Registered: ‎07-30-2018

## Re: Calculate Measure that is duplicated on more than one row

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 12
Registered: ‎06-28-2018

## Re: Calculate Measure that is duplicated on more than one row

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

Community Support Team
Posts: 1,317
Registered: ‎07-30-2018

## Re: Calculate Measure that is duplicated on more than one row

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Established Member
Posts: 201
Registered: ‎07-12-2017

## Re: Calculate Measure that is duplicated on more than one row

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

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

Regards,

Affan

New Contributor
Posts: 420
Registered: ‎04-14-2018

## Re: Calculate Measure that is duplicated on more than one row

[ Edited ]

@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])`

Frequent Visitor
Posts: 12
Registered: ‎06-28-2018

## Re: Calculate Measure that is duplicated on more than one row

Thanks a lot , it worked perfectly