Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Quest
Advocate I
Advocate I

Problem with calculating values

There is a calculation I am trying to achieve:

The data in my report is something like below:

DateProductType#Subscribed
7/1/2021AWeb2
7/1/2021ADesktop5
7/1/2021BWeb10
7/1/2021BDesktop4
7/1/2021CWeb9
7/1/2021CDesktop2
7/1/2021DWeb10
7/1/2021EWeb1
8/1/2021AWeb10
8/1/2021BWeb10
8/1/2021BDesktop5
8/1/2021CWeb9
8/1/2021DDesktop4
8/1/2021DDesktop3
8/1/2021EWeb1

 

I have to find the number of Subscribers for each product in each month irrespective of the Type. Something like below

DateProductType#Subscribed#SubscribedIrrespectiveofType
7/1/2021AWeb27
7/1/2021ADesktop57
7/1/2021BWeb1014
7/1/2021BDesktop414
7/1/2021CWeb911
7/1/2021CDesktop211
7/1/2021DWeb1010
7/1/2021EWeb11
8/1/2021AWeb1010
8/1/2021BWeb1015
8/1/2021BDesktop515
8/1/2021CWeb99
8/1/2021DDesktop47
8/1/2021DDesktop37
8/1/2021EWeb11

 

The final report should show only a count of products against each month and each type which has more than 10 subscribers.

DateProductType#Subscribed#SubscribedIrrespectiveofType
7/1/2021BWeb1014
7/1/2021BDesktop414
7/1/2021CWeb911
7/1/2021CDesktop211
7/1/2021DWeb1010
8/1/2021AWeb1010
8/1/2021BWeb1015
8/1/2021BDesktop515

 

Final Result:

DateType# of Product
7/1/2021Web3
8/1/2021Web2
7/1/2021Desktop3
8/1/2021Desktop2

Please help with how I can acheive this.

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@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] )
)

 

smpa01_0-1634046403118.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

4 REPLIES 4
smpa01
Super User
Super User

@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] )
)

 

smpa01_0-1634046403118.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Thanks a lot. This works.

Quest
Advocate I
Advocate I

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.

 

DateType# of Product
7/1/2021Web3
7/1/2021Desktop3
8/1/2021Web2
8/1/2021Desktop2
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but check the below picture and the attached pbix file.

 

 

Picture1.png

 

No. of Product : =
VAR newtable =
FILTER (
ADDCOLUMNS (
VALUES ( Data[Product] ),
"@subscribedIrrespectiveofType", CALCULATE ( SUM ( Data[#Subscribed] ), ALL ( Data[Type] ) )
),
[@subscribedIrrespectiveofType] >= 10
)
RETURN
IF ( HASONEVALUE ( Data[Date] ), COUNTROWS ( newtable ) )
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.