Forum Discussion

Quest's avatar
Quest
Icon for Advocate I rankAdvocate 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: Date Product Type #Subscribed 7/1/2021 A Web 2 7/1/2021 A Desktop 5 7/1/2021 B W...
  • 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] )
)

     

     

Quest's avatar
Quest
Icon for Advocate I rankAdvocate 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