Forum Discussion

ckhoo2007's avatar
ckhoo2007
Icon for Helper I rankHelper I
5 years ago
Solved

DISTINCOUNT IF certain condition met

Hi All, I have a table which has Date, Client and Sales columns. The Date column has relationship with "Date" table and I have a date slicer to select certain date range. I have created 3 measures ...
  • v-janeyg-msft's avatar
    5 years ago

    Hello, ckhoo2007 

     

    I create two measures to sum total in pie chart. I also modified the total to make it display correctly. You can check the below sample file.

    Like this:

     

    Measure1 = 
    SUMX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                SUMMARIZE (
                    ALLSELECTED(  Sales ),
                    Sales[Client],
                    "t30",
                        CALCULATE (
                            DIVIDE (
                                SUMX ( 'Sales', 'Sales'[Sales] * 'Sales'[Transactions] ),
                                SUM ( 'Sales'[Transactions] )
                            ),
                            DATESINPERIOD ( Sales[Date], MAX ( Sales[Date] ), -30, DAY )
                        ),
                    "t60",
                        CALCULATE (
                            DIVIDE (
                                SUMX ( 'Sales', 'Sales'[Sales] * 'Sales'[Transactions] ),
                                SUM ( 'Sales'[Transactions] )
                            ),
                            DATESINPERIOD ( Sales[Date], MAX ( Sales[Date] ) - 30, -30, DAY )
                        )
                ),
                "t30-t60", [t30] - [t60]
            ),
            "bb", IF ( [t30-t60] > 0, 1 )
        ),
        [bb]
    )
    

     

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.


    Best Regards,

    Community Support Team _ Janey