cancel
Showing results for
Did you mean:
Frequent Visitor

## 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
1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

## 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

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

6 REPLIES 6
Community Support Team

## 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

## 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

Highlighted
Community Support Team

## 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

## 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

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

New Contributor

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

@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

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

Thanks a lot , it worked perfectly

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 290 members 2,855 guests
Recent signins: