Sum of Distinct COunt

[Anonymous]

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

[PaulDBrown · Accepted solution]

@Anonymous 

Try:

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

2) 

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

[Anonymous]

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

[PaulDBrown]

@Anonymous 

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 )

[BA_Pete]

Hi @Anonymous ,

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