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].
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
- ckhoo20075 years ago
Helper I
Sorry some correction, when I try to plot the improved or degraded in a chart it all shows improved.....
- ckhoo20075 years ago
Helper I
Thanks Nidhi, let me work out the sample data set and share later.
- NidhiBhusari5 years ago
Helper IV
Hey ckhoo2007 I tried implementing this scenario that "when I try to plot the improved or degraded in a chart it all shows improved....." using pie chart and as per my analysis I think if your measure generates negative values and are used in pie chart then it won't appear in visual.