Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I would like to count the number of repetitions in function of my columns Purchase Doc. on PoweBI
In excel, I use the formula NB.SI
Pur. Doc. Itm | date | qty | number |
601 | 13.02.2020 | 2 | 1 |
601 | 13.02.2020 | 2 | 2 |
601 | 26.09.2019 | 8 | 3 |
601 | 15.11.2019 | 8 | 4 |
601 | 25.10.2019 | 10 | 5 |
602 | 15.11.2019 | 10 | 1 |
602 | 28.11.2019 | 7 | 2 |
602 | 10.12.2019 | 7 | 3 |
602 | 23.12.2019 | 10 | 4 |
602 | 20.01.2020 | 10 | 5 |
603 | 21.02.2020 | 5 | 1 |
603 | 23.03.2020 | 10 | 2 |
603 | 14.04.2020 | 10 | 3 |
604 | 29.05.2020 | 10 | 1 |
604 | 26.06.2020 | 10 | 2 |
607 | 26.09.2019 | 3 | 1 |
607 | 22.04.2020 | 1 | 2 |
610 | 10.09.2019 | 2 | 1 |
610 | 25.10.2019 | 5 | 2 |
612 | 15.11.2019 | 5 | 1 |
613 | 28.11.2019 | 8 | 1 |
My goal is to calculate aumatically the columns "Number"
Do you have a idea of the function DAX to used ?
Thank you very much for your help, Best regards,
Solved! Go to Solution.
Hi @LudivineLOU ,
To get the same expected result as the table, you need to add an index column manually in power query first:
Then create a calulated column like this:
Column =
CALCULATE (
COUNT ( 'Table'[Pur.Doc.Itm] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Pur.Doc.Itm] ),
'Table'[Index] <= EARLIER ( 'Table'[Index] )
)
)
You will get the expected result:
Attached my sample file that hopes to help you, please check and try it: count.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
THANK YOU very much everbody, it works 🙂
have a nice day,
Best regards,
Hi,
Try this M code
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Pur. Doc. Itm", Int64.Type}, {"date", type date}, {"qty", Int64.Type}}),
Partition = Table.Group(#"Changed Type", {"Pur. Doc. Itm"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"date", "qty", "Index"}, {"date", "qty", "Index"})
in
#"Expanded Partition"
Hope this helps.
Hi @LudivineLOU ,
To get the same expected result as the table, you need to add an index column manually in power query first:
Then create a calulated column like this:
Column =
CALCULATE (
COUNT ( 'Table'[Pur.Doc.Itm] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Pur.Doc.Itm] ),
'Table'[Index] <= EARLIER ( 'Table'[Index] )
)
)
You will get the expected result:
Attached my sample file that hopes to help you, please check and try it: count.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@LudivineLOU , As dates are repeating you might need a index column . or use rank with a tie breaker
countx(filter(Table, table[Pur. Doc.] =earlier(table[Pur. Doc.] ) && table[Itm date] <=earlier(table[Itm date])
&& table[index] <=earlier(table[index]) ),table[Pur. Doc.] )
https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
@LudivineLOU - You need Cthulhu. https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |