Forum Discussion
Helper ITOPN used with periodicity data
Hi,
I've learned how to use TOPN function and how to calculate month by month the cost of the vendor, starting from the table below
| Name | Cost | Start period | End period |
| Vendor1 | 500 | 01.01.2022 | 30.06.2022 |
| Vendor1 | 100 | 01.01.2022 | 30.03.2022 |
| Vendor1 | 1000 | 01.07.2022 | 30.09.2022 |
| Vendor2 | 2000 | 01.01.2022 | 30.06.2022 |
| Vendor2 | 200 | 01.07.2022 | 31.12.2022 |
| Vendor3 | 300 | 01.01.2022 | 30.06.2022 |
| Vendor3 | 400 | 01.01.2022 | 30.03.2022 |
| Vendor3 | 500 | 01.07.2022 | 30.09.2022 |
| Vendor4 | 200 | 01.01.2022 | 30.06.2022 |
| Vendor4 | 300 | 01.07.2022 | 31.12.2022 |
| Vendor4 | 400 | 01.09.2022 | 31.12.2022 |
| Vendor4 | 500 | 01.01.2022 | 31.12.2022 |
Now I have to combine the two:
taking into consideration a filter/slicer that select 1 month, how to retrieve the 2 top costly vendor with the related month cost for this month considering to summarize the vendor costs only if they are considered in this particular month?
The costs are flat (meaning, for ex. for January, I will have the following total cost:
| 500 | 100 | 2000 | 300 | 400 | 500 |
= 3800
As a result, TOPN for Jan need to return me: vendor2= 2000 and vendor3,4=700
Hi,
Please check the below picture and the attached pbix file.
All measures are in the pbix file.
Top two only: = VAR newtable = ADDCOLUMNS ( ALL ( Vendors[Name] ), "@cost", [Cost total:] ) RETURN CALCULATE ( [Cost total:], KEEPFILTERS ( TOPN ( 2, newtable, [@cost], DESC ) ) )
1 Reply
- Jihwan_Kim
Super User
Hi,
Please check the below picture and the attached pbix file.
All measures are in the pbix file.
Top two only: = VAR newtable = ADDCOLUMNS ( ALL ( Vendors[Name] ), "@cost", [Cost total:] ) RETURN CALCULATE ( [Cost total:], KEEPFILTERS ( TOPN ( 2, newtable, [@cost], DESC ) ) )