Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
Here is my today's headlock.
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
Hi @anne-sophie ,
Sorry currently , I could only do this:
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |