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 ...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    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

     

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, ckhoo2007 

 

There is no context in the pie chart, so there may be problems with the value of the measure.

There is no problem with the table you shared, because these columns are all measures. Can you share a sample fake data sample? So we can help you soon.

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

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

Thanks Janey and Nidhi.

I have the sample database in excel and sample pbix, stupid question, how do I attached here?

 

Regards,

Choo