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.
Hi,
I have a large data set of transactions with information regarding Item, Stores and Month.
I need to calculate, for each row, what is the amount of unique months that are associated with that row.
For example for Item 10, Store 1200, there are 2 months (3 and 4), so the number I am looking to obtain is 2, for each row.
For Item 12, Store 1100, there are 6 transactions (rows) but only 3 unique months (1, 9 and 10) so the result I am looking for is 3 for each of the rows.
As you can see from the data, I am able to do the calculation in excel using countifs and sumif, but I cant seem to do it in Power BI. I have over 10 million transactions, so I cant do this calculation in excel.
Can someone help?
Thank you
Hi guys,
I have a large dataset (too big for excel) that I wanted to look at in Power BI.
Each row of data is a transaction. Item number, store and month of transaction.
I need to count the number of different months. So for example for Item = 10, Store = 1200, since a transaction occurs in month 3 and 4, the final result should be 2 (in red). For item 12 and store 1100, there are 6 transactions but spread out accros 3 different months, so the answer should be 3 (in red). The colomns after Month are the calculations that i used in excel to obtain the desired outcome.
Can someone recomend a solution in Power BI? I have over 10 million lines of such info.
Much appreciated!
You can use this formula
Column = CALCULATE ( DISTINCTCOUNT ( Table1[Month] ), ALLEXCEPT ( Table1, Table1[Item number], Table1[Stores] ) )
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |