- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
Calculate Measure that is duplicated on more than one row
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
11-30-2018 05:35 AM
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 |
Solved! Go to Solution.
Accepted Solutions
Re: Calculate Measure that is duplicated on more than one row
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-03-2018 02:06 AM
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
All Replies
Re: Calculate Measure that is duplicated on more than one row
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-02-2018 10:32 PM
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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Re: Calculate Measure that is duplicated on more than one row
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-03-2018 12:03 AM
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
Re: Calculate Measure that is duplicated on more than one row
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-03-2018 12:53 AM
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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Re: Calculate Measure that is duplicated on more than one row
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-03-2018 02:06 AM
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
Re: Calculate Measure that is duplicated on more than one row
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-03-2018 02:44 AM - edited 12-03-2018 02:45 AM
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])
Re: Calculate Measure that is duplicated on more than one row
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-03-2018 03:49 AM
Thanks a lot , it worked perfectly