Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
hi every one,
i am a newbie with Power BI. can you please help me with this count calculation?
i have this data
date | user | product | sale |
1/1/2023 | A | cola | 1 |
1/2/2023 | B | cola | 1 |
1/2/2023 | C | cola | 1 |
1/1/2023 | D | cola | 1 |
1/1/2023 | D | cola | 1 |
1/2/2023 | E | cola | 1 |
1/2/2023 | F | cola | 1 |
1/2/2023 | E | pepsi | 1 |
1/2/2023 | F | pepsi | 1 |
now i want to count howmany user sell the same number of bottle in Jan and Feb 2023. the result i would like to have is:
thank you
month | number of bottles | number of user |
1.2023 | 1 | 1 |
1.2023 | 2 | 1 |
2.2023 | 1 | 2 |
2.2023 | 2 | 2 |
Solved! Go to Solution.
Hi @phalondon ,
Please try below steps:
1. below is my test table
Table:
2. add a new column with below dax formula
month = YEAR([date])&"."&MONTH([date])
3. create a measure with below dax formula
Measure =
VAR cur_month =
SELECTEDVALUE ( 'Table'[month] )
VAR cur_sale =
SELECTEDVALUE ( 'Table'[sale] )
VAR tmp =
CALCULATETABLE (
VALUES ( 'Table'[user] ),
FILTER ( ALL ( 'Table' ), [month] = cur_month && [sale] = cur_sale )
)
RETURN
COUNTROWS ( tmp )
4. add a table visual with fields and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hello
Hi @phalondon ,
Please try below steps:
1. below is my test table
Table:
2. add a new column with below dax formula
month = YEAR([date])&"."&MONTH([date])
3. create a measure with below dax formula
Measure =
VAR cur_month =
SELECTEDVALUE ( 'Table'[month] )
VAR cur_sale =
SELECTEDVALUE ( 'Table'[sale] )
VAR tmp =
CALCULATETABLE (
VALUES ( 'Table'[user] ),
FILTER ( ALL ( 'Table' ), [month] = cur_month && [sale] = cur_sale )
)
RETURN
COUNTROWS ( tmp )
4. add a table visual with fields and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.