cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ritasweidy Frequent Visitor
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)

 

datesalesmanTypeTotalTarget
02/01/2017e1New134.0046,000.00
02/01/2017e2New1,022.7319,000.00
02/01/2017e3Renewal515.000.00
03/01/2017e1New208.1546,000.00
03/01/2017e2New259.0919,000.00
03/01/2017e3New415.0035,000.00
03/01/2017e3Renewal573.000.00
04/01/2017e1New2,152.0046,000.00
04/01/2017e2New259.0919,000.00
05/01/2017e1New2,578.0046,000.00
05/01/2017e2New972.7319,000.00
05/01/2017e3New625.0035,000.00
05/01/2017e3Renewal770.000.00
06/01/2017e1New2,750.5046,000.00
06/01/2017e2New668.1819,000.00
06/01/2017e3New115.0035,000.00
08/02/2017e3New25018498
08/02/2017e3Renewal125.450
10/02/2017e3New73518498
10/02/2017e3Renewal946.960
11/02/2017e3New170018498
11/02/2017e3Renewal23650
13/02/2017e1New1998.554991
13/02/2017e2New140.9120000
13/02/2017e3New894.518498
13/02/2017e3Renewal21500
14/02/2017e1New199754991
14/02/2017e2New77.2720000
14/02/2017e3New484.5518498
14/02/2017e3Renewal8200
15/02/2017e1New956.7554991
15/02/2017e2New-259.0920000
1 ACCEPTED SOLUTION

Accepted Solutions
affan Established Member
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 thissummarize averagex.png

 

 

 

 

 

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
Highlighted
Community Support Team
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.
ritasweidy Frequent Visitor
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

Community Support Team
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.
affan Established Member
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 thissummarize averagex.png

 

 

 

 

 

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

themistoklis New Contributor
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])

 

ritasweidy Frequent Visitor
Frequent Visitor

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

Thanks a lot , it worked perfectly

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 148 members 2,084 guests
Please welcome our newest community members: