cancel
Showing results for
Did you mean:
Frequent Visitor

## Multisteps dynamic countings

Hello,

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

 Order Order attribute 1 Order attribute 2 Product Product attribute 1 O1 OA1.value 1 OA2.value 1 P1 PA1.value 1 O1 OA1.value 1 OA2.value 2 P2 PA1.value 1 O2 OA1.value 1 OA2.value 1 P3 PA1.value 2

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

 Order Order attribute 1 Order attribute 2 Product Product attribute 1 Nb Prod per Order O1 OA1.value 1 OA2.value 1 P1 PA1.value 1 2 O1 OA1.value 1 OA2.value 2 P2 PA1.value 1 2 O2 OA1.value 1 OA2.value 1 P3 PA1.value 2 1

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 Order number of order 1 1 2 1

Having that would be great.

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

 Sale Nb of product per sale S1 3 S2 1

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 Prod number of order number of sale 1 1 1 2 1 0 3 0 1

All help appreciated!

Thanks,

Anne-Sophie

4 REPLIES 4
Community Support

Hi @anne-sophie ,

Sorry currently , I could only do this:

``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.

Frequent Visitor

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

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.

Frequent Visitor

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 !

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks