Vendor category based on their monthly average

Omaralfalah — Wed, 19 Jun 2024 14:20:29 GMT

Hello everyone,

I'm seeking your kind support to assist me in the below:

I have a data table that contain :
- service order numbers ( some of them completed & approved and some not )
- service order status
- service provider "vendor name"
- service actual end date
- service invoice approved date
- column shows the time taken for each approved invoice from service actual end date ( service invoice approved date - service actual end date )

I would like to have stacked bar chart that shows the count of service providers " vendor name" in each month based on service invoice approved date and based their monthly average for their approved invoices under which of the following category they fall? ( 1 to 20 days or 21 to 35 days or more than 35 days ) .

please note that I do not want to have same vendor to be repeated in two categories in one month.

Re: Vendor category based on their monthly average

Anonymous — Thu, 20 Jun 2024 06:33:05 GMT

Hi Omaralfalah ,

I created some data:

Here are the steps you can follow：

1. Create calculated table

Table 2 = CALENDAR( DATE(2024,1,1), DATE(2024,12,31))

2. Create calculated column.

test = var _mindate= MINX(FILTER(ALL('Table'),'Table'[vendor name]=EARLIER('Table'[vendor name])),[service invoice approved date]) var _maxdate= MAXX(FILTER(ALL('Table'),'Table'[vendor name]=EARLIER('Table'[vendor name])),[service actual end date]) var _table1= FILTER( 'Table 2','Table 2'[Date]>=_mindate&&'Table 2'[Date]<=_maxdate) var _table2= SUMMARIZE(_table1,[Month]) return CONCATENATEX( _table2,[Month],"-" )

3. Create measure.

count of service providers = var _column= CONCATENATEX( 'Table',[test],"/") return CALCULATE( DISTINCTCOUNT('Table'[vendor name]), FILTER(ALL('Table'), CONTAINSSTRING( 'Table'[test],MAX('Table 2'[Month]))=TRUE()) )

category = var _mindate=MINX(FILTER(ALL('Table'),'Table'[vendor name]=MAX('Table'[vendor name])),[service invoice approved date]) var _maxdate=MAXX(FILTER(ALL('Table'),'Table'[vendor name]=MAX('Table'[vendor name])),[service actual end date]) var _count= CALCULATE( DISTINCTCOUNT('Table 2'[Month]), FILTER('Table 2', 'Table 2'[Date]>=_mindate&&'Table 2'[Date]<+_maxdate)) var _sum= SUMX( FILTER(ALL('Table'),'Table'[vendor name]=MAX('Table'[vendor name])),[datediff]) var _divide= DIVIDE( _sum, _count) RETURN SWITCH( TRUE(), _divide>=1&&_divide<=20,"1 to 20 days", _divide>=21&&_divide<=35,"21 to 35 days", "more than 35 days")

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

topic Re: Vendor category based on their monthly average in DAX Commands and Tips

Vendor category based on their monthly average

Re: Vendor category based on their monthly average