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
anne-sophie
Frequent Visitor

Multisteps dynamic countings

Hello,

 

Here is my today's headlock.

I have a table, of, let's say, orders and products, and attributes:

OrderOrder attribute 1Order attribute 2ProductProduct attribute 1
O1OA1.value 1OA2.value 1P1PA1.value 1
O1OA1.value 1OA2.value 2P2PA1.value 1
O2OA1.value 1OA2.value 1P3PA1.value 2

 

In a first step, I'd like to count the number of products per order.

OrderOrder attribute 1Order attribute 2ProductProduct attribute 1Nb Prod per Order
O1OA1.value 1OA2.value 1P1PA1.value 12
O1OA1.value 1OA2.value 2P2PA1.value 12
O2OA1.value 1OA2.value 1P3PA1.value 21

This counting has to be dynamic, and adjust according to filters set by users.

 

In a second step, I'd need to summarize and count the number of orders per the previous calculation (and still staying dynamic):

Nb Prod per Ordernumber of order
11
21

 

Having that would be great.

 

But then, I have another table with a count of number of products (let's say sales):

SaleNb of product per sale
S13
S21

At the end of the day, I'd like to summarize both "number of products" columns in a single table, with relavant number of orders and number of sales:

Nb Prodnumber of ordernumber of sale
111
210
301

 

All help appreciated!

Thanks,

Anne-Sophie

4 REPLIES 4
v-eqin-msft
Community Support
Community Support

Hi @anne-sophie ,

 

Sorry currently , I could only do this:

countax.gif

Please follow these steps:

countRows table1 = COUNTROWS('Tableau1')
count1 = 
VAR _T = SUMMARIZE(ALLSELECTED('Tableau1'),'Tableau1'[Product Code],"NEW",[countRows table1])
VAR _A = [countRows table1]
Return
COUNTAX(FILTER(_T,[NEW] = _A),[NEW])
countRows table2 = COUNTROWS('Tableau2')
count2 = 
VAR _T = SUMMARIZE(ALLSELECTED('Tableau2'),'Tableau2'[Product Code],"NEW",[countRows table2])
VAR _A = [countRows table2]
Return
COUNTAX(FILTER(_T,[NEW] = _A),[NEW])

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

Hello Eyelyn,

The result is not what I expected. 

But thanks for having a look at it - your answer brought some new ideas to me.

Best regards,

Anne-Sophie

v-eqin-msft
Community Support
Community Support

Hi @anne-sophie ,

 

Actually, the information you have provided is not making the problem clear to me.

Please explain to me in detail about how to get these fields :Nb Prod per Order / number of order / number of sale or provide me with more details about your table and your problem and share me with your pbix file after removing sensitive data.

 

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

Hello Eyelyn,

You are right, I should explain my case better.

I've shared a .pibx : here

I have a table (Tableau1) with products made of items. 

I have another table (Tableau2) with also products and items, but in this case the number of items per products can be precalculated, before data is in powerbi.

I need to create a visual table with 3 columns: nb of items per products (= max of nb of items per products in both table), number of product code from table1 and number of product code from table2. 

I managed to do it, using calculated tables (summarized and groupby, cf. step1 and step2).

I would like now to have the same calculation, but dynamic regarding filters users can define (on brand, product type, or, for table1, item category). I think this requires to have step1 and 2 done with a measure or a calculated column, but I'm stuck !

Thank you for your help

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.