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.
I'm working on creating a matrix that will look something like this:
Branch Name Monthly Sales % Of Grand Total # Of Sales People
Branch 4 $444,444 36.36% 12
Branch 3 $333,333 27.27% 12
Branch 2 $222,222 18.18% 12
Branch 1 $111,111 9.09% 12
Total Top 4 $1,111,110 90.90% 48
Other Branches $111,110 9.09% 42
Total All Branches $1,222,220 100.00% 90
(I figured up the above numbers manually so I know the final percentage adds up to 99.99% but that's due to my rounding)
After doing some research and watching some videos I figured out how to sort of get the Top 4 part to work using the Top N filter but I end up with a couple of snags. First, here is what I get back:
Branch Name Monthly Sales % Of Grand Total # Of Sales People
Branch 4 $444,444 40.00% 12
Branch 3 $333,333 30.00% 12
Branch 2 $222,222 20.00% 12
Branch 1 $111,111 10.00% 12
Total Top 4 $1,111,110 100.00% 48
I have a table for measures named ReportMeasures. I have a measure that looks like the following:
Technically, it's working correctly except that it's incorrect. The problem I'm having is that the % Of Grand Total column is basing its calculations on the total monthly sales of the top 4 branches, $1,111,110, instead of the real grand total amount which is $1,222,220. I have spent most of today and yesterday trying to figure out how to make a measure and/or column that will look at the real grand total and give me the correct values.
I'm hoping that if someone out there can point me in the right direction on this part then I should be able to figure out the rest.
Thanks in advance,
Scott
Solved! Go to Solution.
@Anonymous , if need grand total to owner the filters
% of Total= Divide(SUM(BRANCH_SALES[TOTAL_SALES]), calculate(SUM(BRANCH_SALES[TOTAL_SALES]), allselected()) )
filter should not be consider
% of Total= Divide(SUM(BRANCH_SALES[TOTAL_SALES]), calculate(SUM(BRANCH_SALES[TOTAL_SALES]), all()) )
Prefer TOPN as visual level filter
TOPN: https://youtu.be/QIVEFp-QiOk
refer if needed
Hi,
Do these measures work?
Monthly sales = SUM(BRANCH_SALES[TOTAL_SALES])
Montly sales of all branches = calculate([Monthly sales],all(BRANCH_SALES[Branch Name]))
% of Grand total = divide([Monthly sales],[Montly sales of all branches])
Hope this helps.
Hi,
Do these measures work?
Monthly sales = SUM(BRANCH_SALES[TOTAL_SALES])
Montly sales of all branches = calculate([Monthly sales],all(BRANCH_SALES[Branch Name]))
% of Grand total = divide([Monthly sales],[Montly sales of all branches])
Hope this helps.
Thank you very much for your help. That was exactly what I needed.
You are welcome.
@Anonymous , if need grand total to owner the filters
% of Total= Divide(SUM(BRANCH_SALES[TOTAL_SALES]), calculate(SUM(BRANCH_SALES[TOTAL_SALES]), allselected()) )
filter should not be consider
% of Total= Divide(SUM(BRANCH_SALES[TOTAL_SALES]), calculate(SUM(BRANCH_SALES[TOTAL_SALES]), all()) )
Prefer TOPN as visual level filter
TOPN: https://youtu.be/QIVEFp-QiOk
refer if needed
I'll have to check out the TOPN function since I think I will need it for the next part of my report.
Thanks!
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 |
---|---|
105 | |
104 | |
87 | |
73 | |
66 |
User | Count |
---|---|
122 | |
112 | |
98 | |
79 | |
72 |