Forum Discussion

Quest's avatar
Quest
Advocate I
4 years ago
Solved

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.

  • smpa01's avatar
    smpa01
    4 years ago

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

     

     

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

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

     

     

     

    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 ) )
     
  • Quest's avatar
    Quest
    Advocate I
    4 years ago

    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
  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

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