Forum Discussion
DISTINCOUNT IF certain condition met
- 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
ckhoo2007 can you try implementing this DAX and use date in a slicer.
Here [Sales Total] is Sales[Diff30-60].
- ckhoo20075 years ago
Helper I
Thanks very much Nidhi, yes it works I mean I can calculate the client however it seems like they all returned as Improved, but when I populate the Diff30-60 I can clearly see positive and negative numbers, any reason why?
Client T-30Sales T-60Sales Diff30-60 2D 25.80517 26.16336 -0.3582 A3 12.25654 12.06679 0.18975 G2 13.00134 12.96319 0.03815 A8 25.75107 25.88478 -0.1337 B2 11.91763 12.65704 -0.73941 C1 10.24266 9.792654 0.45 C2 14.46511 13.35042 1.11469 D2 15.89932 15.62133 0.27799 D3 11.68738 11.94896 -0.26159 D4 12.71874 12.56913 0.14961 G4 11.29752 11.08111 0.21641 G1 8.983207 8.910448 0.07276 J1 15.6621 15.1474 0.5147 L9 33.1128 34.75687 -1.64407 M2 30.38839 30.25823 0.13017 M8 14.73733 15.79288 -1.05555 N3 12.33259 12.29678 0.03582 N2 21.52088 23.32267 -1.80179 I used the following formula:
Improved = IF([Diff30-60]> 0,DISTINCTCOUNT(Sheet1[Client]))
Degraded = IF([Diff30-60]< 0,DISTINCTCOUNT(Sheet1[Client]))
Thanks again.
Regards,
Choo