DAX measure total costs based on filters in dashboard

[miltenburger]

Hi Guys,

I'm working on a project based on costs from my clients. The thing that i come up against is that I have several clients which have debet and credit costs. So first of all, what I want to achieve looks very simple. How many clients do I have that have costs > 0 based on my filters in the dashboard.

I have the following filters: year, product, category, provider.

My dataset:

Date	PersNumer	Product	Category	Costs	Provider
1-1-2017	1	A	First	100	1000
4-3-2017	1	A	First	-100	1000
6-6-2017	2	B	First	600	1043
9-7-2017	2	C	Second	300	1345
10-10-2017	3	C	Second	250	1345
1-3-2018	3	C	Second	250	1200
1-4-2018	2	C	Second	200	1243
2-4-2018	2	C	Second	-200	1000
3-4-2018	1	A	First	200	1001
10-9-2018	1	B	First	450	1001
12-12-2018	2	A	First	100	1001

I already created a dax measure, but it seems to go wrong:

MyMeasure = CALCULATE(DISTINCTCOUNT(PersNumber);costs > 0)

What you can see in my table: there are a few clients that have debet and credit costs. For example client 1, his total costs in 2017 = 0, but based on my measure this clients has got some costs (which is logic because there is 1 line which meets the requirements). But his total = 0, so I don't want him to count as a client with costs for this specific year.

Can you guys help me out with this measure? If you need more information, let me know.

[v-yulgu-msft · Accepted solution]

Hi @miltenburger ,

Please refer to below measures.

sumcost =
CALCULATE (
    SUM ( Data_1[Costs] ),
    FILTER (
        ALLSELECTED ( Data_1 ),
        Data_1[PersNumer] = SELECTEDVALUE ( Data_1[PersNumer] )
    )
)

count client =
CALCULATE (
    DISTINCTCOUNT ( Data_1[PersNumer] ),
    FILTER ( ALLSELECTED ( Data_1 ), [sumcost] > 0 )
)

Best regards,

Yuliana Gu

[miltenburger]

Thank you very much @v-yulgu-msft , this is exactly what I was looking for.

[PattemManohar]

@miltenburger  Please post the sample data that can be copied.

[miltenburger]

@PattemManohar  I've edited my post