Forum Discussion
New column or measure based on two conditions
Hi PBI Experts,
Looking for help with calculating a new column/measure which shows the sum of biggest sales amount per day for John and Matt
Table1
Name Date Sales
John 1/1/23 3
John 1/1/23 2
John 1/2/23 4
Matt 1/1/23 1
Matt 1/1/23 2
Matt 1/2/23 5
Expected result table below
Result Table
Name Sum of biggest Sale
John 4+3
Matt 2+5
In short while calculating sum of biggest sales, it needs to ignore all other sales amount on that day. The values highlighted in red needs to be ignored. Tried allexcpet and filter with no luck.
Topn didn't help.
I ended up creating a new column calculating the max of sales with allexcept name and date. Then created a measure which sum the new column divided by number of dates.
2 Replies
Hi datadog_ab, you could try something like the following:
Sum of 2 Largest Sales = SUMX ( TOPN ( 2, 'Fact Table', 'Fact Table'[Sales], DESC ), 'Fact Table'[Sales] )There are other considerations if you want to include more than 2 sales. You could parameterise this so the use can change the number of sales included. Or if you need to include all sales if there are more than two matches at the price point.
Topn didn't help.
I ended up creating a new column calculating the max of sales with allexcept name and date. Then created a measure which sum the new column divided by number of dates.
