Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello
I am trying to use a distinct count using CALCULATE and FILTER but for some reason is not working:
Measure "$ Net Sales" is = Sales - Return and is working fine, but when I try to count customers (SC_CODIGO) which "$ Net Sales" is > 0 it counts customers which Sales is > 0, not net sales.
Thank you in advanced for your help!
Solved! Go to Solution.
Hmm. That's strange unless [Return] and [Sales] are measures too. (I was assuming they were columns.)
If they are measures, then I'd recommend a different approach:
#C Net =
SUMX (
DISTINCT ( 'Venta Neta'[SC_CODIGO] ),
IF ( [Return] > [Sales], 1 )
)
You have to be careful using measures inside of a FILTER since the measure is evaluated after a row context to filter context transition.
I'd recommend avoiding it where you can and instead write:
#C Net =
CALCULATE (
DISTINCTCOUNT ( 'Venta Neta'[SC_CODIGO] ),
FILTER ( 'Venta Neta', 'Venta Neta'[Return] > 'Venta Neta'[Sales] )
)
Thank you Alexis for your help, now "#C Neto" column is ok, but the total number is counting all rows, it shoud be 669 (which have 1 in #C Neto column) and not 749.
Regards
Hmm. That's strange unless [Return] and [Sales] are measures too. (I was assuming they were columns.)
If they are measures, then I'd recommend a different approach:
#C Net =
SUMX (
DISTINCT ( 'Venta Neta'[SC_CODIGO] ),
IF ( [Return] > [Sales], 1 )
)
Thank you very much for your help! now is working!
Regards
User | Count |
---|---|
76 | |
75 | |
46 | |
31 | |
27 |
User | Count |
---|---|
99 | |
91 | |
51 | |
49 | |
45 |