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 dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
There is a calculation I am trying to achieve:
The data in my report is something like below:
Date | Product | Type | #Subscribed |
7/1/2021 | A | Web | 2 |
7/1/2021 | A | Desktop | 5 |
7/1/2021 | B | Web | 10 |
7/1/2021 | B | Desktop | 4 |
7/1/2021 | C | Web | 9 |
7/1/2021 | C | Desktop | 2 |
7/1/2021 | D | Web | 10 |
7/1/2021 | E | Web | 1 |
8/1/2021 | A | Web | 10 |
8/1/2021 | B | Web | 10 |
8/1/2021 | B | Desktop | 5 |
8/1/2021 | C | Web | 9 |
8/1/2021 | D | Desktop | 4 |
8/1/2021 | D | Desktop | 3 |
8/1/2021 | E | Web | 1 |
I have to find the number of Subscribers for each product in each month irrespective of the Type. Something like below
Date | Product | Type | #Subscribed | #SubscribedIrrespectiveofType |
7/1/2021 | A | Web | 2 | 7 |
7/1/2021 | A | Desktop | 5 | 7 |
7/1/2021 | B | Web | 10 | 14 |
7/1/2021 | B | Desktop | 4 | 14 |
7/1/2021 | C | Web | 9 | 11 |
7/1/2021 | C | Desktop | 2 | 11 |
7/1/2021 | D | Web | 10 | 10 |
7/1/2021 | E | Web | 1 | 1 |
8/1/2021 | A | Web | 10 | 10 |
8/1/2021 | B | Web | 10 | 15 |
8/1/2021 | B | Desktop | 5 | 15 |
8/1/2021 | C | Web | 9 | 9 |
8/1/2021 | D | Desktop | 4 | 7 |
8/1/2021 | D | Desktop | 3 | 7 |
8/1/2021 | E | Web | 1 | 1 |
The final report should show only a count of products against each month and each type which has more than 10 subscribers.
Date | Product | Type | #Subscribed | #SubscribedIrrespectiveofType |
7/1/2021 | B | Web | 10 | 14 |
7/1/2021 | B | Desktop | 4 | 14 |
7/1/2021 | C | Web | 9 | 11 |
7/1/2021 | C | Desktop | 2 | 11 |
7/1/2021 | D | Web | 10 | 10 |
8/1/2021 | A | Web | 10 | 10 |
8/1/2021 | B | Web | 10 | 15 |
8/1/2021 | B | Desktop | 5 | 15 |
Final Result:
Date | Type | # of Product |
7/1/2021 | Web | 3 |
8/1/2021 | Web | 2 |
7/1/2021 | Desktop | 3 |
8/1/2021 | Desktop | 2 |
Please help with how I can acheive this.
Solved! Go to Solution.
@Quest please try this
Measure =
CALCULATE (
CALCULATE (
DISTINCTCOUNT ( 'Table'[Product] ),
CALCULATETABLE (
'Table',
FILTER (
ADDCOLUMNS (
'Table',
"@count",
CALCULATE (
SUM ( 'Table'[#Subscribed] ),
ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Product] )
)
),
[@count] >= 10
)
)
),
ALLEXCEPT ( 'Table', 'Table'[Date] )
)
@Quest please try this
Measure =
CALCULATE (
CALCULATE (
DISTINCTCOUNT ( 'Table'[Product] ),
CALCULATETABLE (
'Table',
FILTER (
ADDCOLUMNS (
'Table',
"@count",
CALCULATE (
SUM ( 'Table'[#Subscribed] ),
ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Product] )
)
),
[@count] >= 10
)
)
),
ALLEXCEPT ( 'Table', 'Table'[Date] )
)
Thanks a lot. This works.
Thank you for the response but this does not work in my case. In my final output, I want the #of products repeated for each Type for a month. In this case for 7/1/2021, the count of distinct products is 3 and this value should be shown against each Type in the Month, as shown below.
Date | Type | # of Product |
7/1/2021 | Web | 3 |
7/1/2021 | Desktop | 3 |
8/1/2021 | Web | 2 |
8/1/2021 | Desktop | 2 |
Hi,
I am not sure if I understood your question correctly, but check the below picture and the attached pbix file.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
15 | |
9 | |
7 | |
7 | |
6 |
User | Count |
---|---|
22 | |
11 | |
10 | |
10 | |
8 |