cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
volesh Frequent Visitor
Frequent Visitor

The same items into sales orders/ concurrent parts

Hi all!

My question is, how to create something like groups for different items into different orders?
For example, I have the data source:

Order#OrderDateItemQty
101.01.2019apple3
202.01.2019apple3
202.01.2019orange2
303.01.2019apple2
303.01.2019banana3
303.01.2019orange4
303.01.2019pear2

 

 

Apple.pngI want to show:
The first card/BI component TOP item: Apples - were sold three times
-----photo
Second: The TOP pair for Apple is Orange - were sold together two times per period.
Third:
In addition, the apples were sold with products: banana, pear - together one time per period.

Of course, I have much more data (For example: Apples were sold with 20 different items together 1000 times per period), so I need to create the filter where I can choose count of orders when products were sold together.
- more than X times (orders) per period or between X-Y.
In my example, If I don't want to see products that were sold less than twice with my TOP(apple) I change slicer condition and the report shows me just Orange.
Can you help me?

Many thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: The same items into sales orders/ concurrent parts

hi, @volesh 

After my research, you could try this way:

Step1:

Add a rank measure by Item

Rank = RANKX(ALLSELECTED(Table1[Item]),CALCULATE(COUNTA(Table1[Item])),,,Dense)

Step2:

Create a filter by measure for third card.

filter by = var _top1=CALCULATE(MAXX(TOPN(1,ALLSELECTED(Table1[Item]),[Rank],ASC),[Item]))
var _orderno=CALCULATETABLE(VALUES(Table1[Order#]),FILTER(ALLSELECTED(Table1),Table1[Item]=_top1)) 
var _table =FILTER(SUMMARIZE(Table1,Table1[Order#],Table1[Item]),Table1[Order#] in _orderno)
return
COUNTAX(FILTER(_table,[Item]<>_top1),[Item])

Step3:

Now create three cards.

First card: Darg Item and count measure into card visual and then drag rank measure into visual level filter and set is 1

Second card: Darg Item and count measure into card visual and then drag rank measure into visual level filter and set is 2

Third card: Darg Item and count measure into card visual and then drag filter by measure into visual level filter and set is more than X times (orders) per period or between X-Y.

Result:

10.JPG

and here is pbix file, please try it.

 

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.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: The same items into sales orders/ concurrent parts

hi, @volesh 

After my research, you could try this way:

Step1:

Add a rank measure by Item

Rank = RANKX(ALLSELECTED(Table1[Item]),CALCULATE(COUNTA(Table1[Item])),,,Dense)

Step2:

Create a filter by measure for third card.

filter by = var _top1=CALCULATE(MAXX(TOPN(1,ALLSELECTED(Table1[Item]),[Rank],ASC),[Item]))
var _orderno=CALCULATETABLE(VALUES(Table1[Order#]),FILTER(ALLSELECTED(Table1),Table1[Item]=_top1)) 
var _table =FILTER(SUMMARIZE(Table1,Table1[Order#],Table1[Item]),Table1[Order#] in _orderno)
return
COUNTAX(FILTER(_table,[Item]<>_top1),[Item])

Step3:

Now create three cards.

First card: Darg Item and count measure into card visual and then drag rank measure into visual level filter and set is 1

Second card: Darg Item and count measure into card visual and then drag rank measure into visual level filter and set is 2

Third card: Darg Item and count measure into card visual and then drag filter by measure into visual level filter and set is more than X times (orders) per period or between X-Y.

Result:

10.JPG

and here is pbix file, please try it.

 

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.

View solution in original post

Highlighted
volesh Frequent Visitor
Frequent Visitor

Re: The same items into sales orders/ concurrent parts

@v-lili6-msft 
Thanks a lot for your solution.
But unfortunately, it isn't enough for me...
In my data, I have a few items for the first card, because of the equal count of items.
1. Card #1
  I want to show just one. Do I need to add a ranking (new_Rank) taking into account the current ranking?
for ex:
source

Order#OrderDateItemQty
101.01.2019apple3
202.01.2019apple3
202.01.2019orange2
303.01.2019orange2
403.01.2019apple3
403.01.2019orange4
403.01.2019pear2


Rank:

ItemCnt of OrdersCurrent RankNew Rank
apple311
orange312


Card #2. 
The same question. There may be many values in rank 2. On a card, I want to show just one but I need to be able to show all of them.
It is worth noting, that item "orange" has rank 1 but has to be on Card#2, because "apple" is on the first card.

I tried to change DAX (rank)

=RANKX(ALLSELECTED('test data'[Item_code]), (CALCULATE(DISTINCTCOUNT('test data'[order_ID])) + int(CALCULATE(MIN('test data'[OrderDate])))/100000 ) ,,DESC,Dense )
and it works, but just with [OrderDate]... and I dont know how to do DAX with [Item] - 'text' data type. In addition, I have items code like - "# 12qwer", "$asd 1" and other.
Can you help me one more time?

volesh Frequent Visitor
Frequent Visitor

Re: The same items into sales orders/ concurrent parts

Thank you very much @v-lili6-msft 
I marked your solution.
If you are still looking at my post, I will add one more question:
How can I use the measure filter by for each item on a card?
For example on a second card I want to show:
Item #, the number of orders an item, and the number of shared purchases.

Order#OrderDateItemQty
101.01.2019apple3
202.01.2019apple3
202.01.2019orange2
303.01.2019orange2
403.01.2019apple3
403.01.2019orange4
403.01.2019pear2

Expected result:
Card#1: Apple - sold 3 times
Card#2: orange - sold 3 times, 2  - the number of shared purchases with apple.
Card#3: pear - sold 1 time, 0  - the number of shared purchases with apple, 0  - the number of shared purchases with any other products

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,996)