Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Anonymous
Try:
1) Sum of revenue = SUM(Table[Revenue])
2)
Store count =
CALCULATE (
DISTINCTCOUNT ( Table[Store] ),
FILTER ( Table, NOT ( ISBLANK ( [Sum of revenue] ) ) )
)
Proud to be a Super User!
Paul on Linkedin.
@Anonymous
Try:
1) Sum of revenue = SUM(Table[Revenue])
2)
Store count =
CALCULATE (
DISTINCTCOUNT ( Table[Store] ),
FILTER ( Table, NOT ( ISBLANK ( [Sum of revenue] ) ) )
)
Proud to be a Super User!
Paul on Linkedin.
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
@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 )
Proud to be a Super User!
Paul on Linkedin.
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |