cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dennis_tegut
Helper II
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:

dennis_tegut_2-1612972388655.png

 

 

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
PaulDBrown
Super User II
Super User II

@dennis_tegut 

Try:

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

2) 

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




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

4 REPLIES 4
PaulDBrown
Super User II
Super User II

@dennis_tegut 

Try:

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

2) 

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




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

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

@dennis_tegut 

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 )

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






BA_Pete
Super User II
Super User II

Hi @dennis_tegut ,

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors