Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
volesh
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
v-lili6-msft
Community Support
Community Support

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
v-lili6-msft
Community Support
Community Support

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.

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

@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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.