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?
Solved! Go to Solution.
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?
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 )
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.
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates