Forum Discussion
unique month calculation
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
2 Replies
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] ) )
