cancel
Showing results for
Did you mean:  Helper II

## Sum of Distinct COunt

Hello,

I have data with revenue for different stores, trade groups and articles. I want to show that data in a matrix like this: Fields Price and Revenue is in the data. Field Count(wanted) is the value i want to get. Field Count(achieved) is a Measure i added and does not quite what i want it to. Here is the formula:

IF(  ISBLANK(  SUM(REVENUE) ), BLANK( ), DISTINCTCOUNT( STORE )

So what i want to show is the count of stores that had a revenue for each level. Yet a store can just be counted once at each level. As you can see, for the store level the output is correct as just the stores with a revenue get counted. The sum for each Article then is wrong as it just gives the count of every store without ignoring the ones without revenue.

For level trade group i think there is another problem. For example store 1 has revenue for Apples and Bananas, yet it shoud just count as 1 store on trade group level.

I hope i explained my problem good enough. Can anyone help me with this one?

Regards,

Dennis

1 ACCEPTED SOLUTION  Super User II

Try:

1) Sum of revenue = SUM(Table[Revenue])

2)

``````Store count =
CALCULATE (
DISTINCTCOUNT ( Table[Store] ),
FILTER ( Table, NOT ( ISBLANK ( [Sum of revenue] ) ) )
)``````

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

4 REPLIES 4  Super User II

Try:

1) Sum of revenue = SUM(Table[Revenue])

2)

``````Store count =
CALCULATE (
DISTINCTCOUNT ( Table[Store] ),
FILTER ( Table, NOT ( ISBLANK ( [Sum of revenue] ) ) )
)``````

In doing so, you are also helping me. Thank you!

Proud to be a Super User!  Helper II

Hello @PaulDBrown ,

Thanks, your solution gets me were i want to be. Yet, i get another issue. The performance of my visual explodes when i add the measure. Without the measure my performance analyser shows a loading time of 1 - 2 sec. With the measure loading time is > 25 secs. Is there a more performant solution?

Regards

Dennis  Super User II

Wow! that´s quite an issue.

You might try the following, though I suspect it won't improve performance:

``````Store count =
VAR stores =
CALCULATETABLE (
VALUES ( Table[Store] ),
FILTER ( Table, NOT ( ISBLANK ( [Sum of revenue] ) ) )
)
RETURN
COUNTROWS ( stores )
``````

In doing so, you are also helping me. Thank you!

Proud to be a Super User!  Super User II

Are you able to share your sample data please? Remove any sensitive information first if you haven't got any dummy data to share.

Thanks,

Pete Announcements #### Welcome to the User Group Public Preview  