[FONT="]Hi, I have an issue with a DAX formula and have spent over a week trying to resolve it.[/FONT]
[FONT="]I would like to know the number of sale transactions each day. But here’s the quandary:[/FONT]
[FONT="]TRANS# DISTINCT COUNT:=CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]>0)-CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]<0)
[/FONT]
[FONT="]Correct answer should be 43 units sold and 32 transactions[/FONT]
[FONT="]The issue is with transaction 206, we had a return and a sale in the same transaction. So it met both criteria being both +1 and -1 = 0[/FONT]
[FONT="] [/FONT]
<colgroup><col><col><col></colgroup><tbody>
</tbody>[FONT="]Thanks[/FONT]
[FONT="]I would like to know the number of sale transactions each day. But here’s the quandary:[/FONT]
- If the total number of units sold after returns is zero, ignore transaction.
- If total units sold is >0 then count it 1
- If total units sold is less <0 count it -1
[FONT="]TRANS# DISTINCT COUNT:=CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]>0)-CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]<0)
[/FONT]
[FONT="]Correct answer should be 43 units sold and 32 transactions[/FONT]
[FONT="]The issue is with transaction 206, we had a return and a sale in the same transaction. So it met both criteria being both +1 and -1 = 0[/FONT]
[FONT="] [/FONT]
Column Labels | ||
9200 | ||
Row Labels | TRANS# DISTINCT COUNT | SALES TOTAL UNITS SOLD |
08/14/18 | ||
172 | 1 | 1 |
173 | -1 | -1 |
174 | 1 | 1 |
175 | 0 | 0 |
176 | 1 | 1 |
177 | 0 | 0 |
178 | 1 | 1 |
179 | 1 | 2 |
180 | 1 | 3 |
181 | 1 | 1 |
182 | 1 | 3 |
183 | 1 | 1 |
184 | 1 | 1 |
185 | 1 | 1 |
205 | 1 | 1 |
206 | 0 | 3 |
207 | 1 | 1 |
208 | 1 | 1 |
209 | 1 | 1 |
210 | 0 | 0 |
211 | 1 | 1 |
212 | 1 | 1 |
213 | 1 | 1 |
214 | 1 | 1 |
215 | 1 | 1 |
216 | 1 | 1 |
217 | -1 | -3 |
218 | 1 | 1 |
219 | 1 | 2 |
220 | 1 | 3 |
221 | 1 | 2 |
222 | 1 | 1 |
223 | 1 | 1 |
224 | 1 | 2 |
225 | 1 | 1 |
226 | 1 | 2 |
227 | 1 | 1 |
228 | 1 | 1 |
229 | 1 | 1 |
08/14/18 Total | 31 | 43 |
<colgroup><col><col><col></colgroup><tbody>
</tbody>