Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey,
I have data from January to September of this year. I have a bar chart and would like a static line for the margin % for the whole year. I know how to do it for the entire company by creating a column, but then it doesn't change for drill downs. The issue is that I want it to change for the roles it is shared with. ie, I want the static line to be Region specific for the region it is shared with. I want the line to be District specific for the district I share with, not just the company margin. Please help. Thanks
Solved! Go to Solution.
@Anonymous
hello, to create a fixed margin:
Margin = DIVIDE ( CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Region], Table1[District] ) ) - CALCULATE ( SUM ( Table1[Cost] ), ALLEXCEPT ( Table1, Table1[Region], Table1[District] ) ), CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Region], Table1[District] ) ) )
@Vvelarde That works too - But why do we need the District?
Margin 2 = DIVIDE ( CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Region] ) ) - CALCULATE ( SUM ( Table1[Cost] ), ALLEXCEPT ( Table1, Table1[Region] ) ), CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Region] ) ) )
EDIT: You can actually revise mine to only this as well
Margin_Region = VAR RegionSales = CALCULATE ( SUM ( Table1[Sales] ), ALLSELECTED(Table1[Month]) ) VAR RegionCost = CALCULATE ( SUM ( Table1[Cost] ), ALLSELECTED(Table1[Month]) ) RETURN DIVIDE ( RegionSales - RegionCost, RegionSales )
@Anonymous Actually the more I test this the more I think you should go with this....
Margin_Region ALS = VAR RegionSales = CALCULATE ( SUM ( Table1[Sales] ), ALLSELECTED(Table1[Month]) ) VAR RegionCost = CALCULATE ( SUM ( Table1[Cost] ), ALLSELECTED(Table1[Month]) ) RETURN DIVIDE ( RegionSales - RegionCost, RegionSales )
Look the picture - you can test this with a matrix visualization - I think the above will give you what you are looking for
If you use Slicers andd a Month Slicer and select only a number of months this will adjust
@Anonymous
I’d like to recommend below two documents to you for the use of Row Level Security (Pro feature). In the examples, different roles are defined for different regions. Then each role member can see partial/all data in the shared dashboard based on his/her region belong to.
https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/
http://blog.pragmaticworks.com/power-bi-role-level-security
Best Regards,
Herbert
I don't understand what this part of your answer does:
The User id's can then be used in the row level security. DAX for RSL will be something like
=USERS[user_ID] = USERNAME()
I get the logic of making a new table and summarizing the District and using the margin for that. This does not work though in my case, since I will be sharing with both Regions AND Districts (subset of region).
@Anonymous hi, sorry, poorly described, i was trying to get across the idea that the user login automatically creates a filter on the model allowing only the margins through that are relevant to them.
You will end up with say two measure:
Actual Measure = [Gross Margin]/[Units Sold]
Target Marging = calculate([Department Margin],FILTER(Table1,Table1[USER] = USERNAME()))
I understand how the roles work. I just need to find a measure that will work calculate the correct measure at both the district and regional level.
@Anonymous
You can create a chart with two levels of region and district. And create a measure to be the value displayed. Then the user can use drill down to see the measure result in two different levels. About the measure formula, we need to write it based on the data tables and the visual chart you desired.
Best Regards,
Herbert
So using this sample .pbix, how do I write a measure for a static line for the whole year to be specific for each row level security I choose over Region and District?
@Anonymous
How about the result using following measure in line charts as below?
Margin_Region = VAR RegionSales = CALCULATE ( SUM ( Table1[Sales] ), VALUES ( Table1[Region] ) ) VAR RegionCost = CALCULATE ( SUM ( Table1[Cost] ), VALUES ( Table1[Region] ) ) RETURN DIVIDE ( RegionSales - RegionCost, RegionSales )
Best Regards,
Herbert
I need it to be a static line for the year. i.e. if some district's YTD margin is 52%, it shows at 52% across the board, not changing monthly. If a region's YTD margin is 48%, it shows 48% across the board.
@Anonymous
hello, to create a fixed margin:
Margin = DIVIDE ( CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Region], Table1[District] ) ) - CALCULATE ( SUM ( Table1[Cost] ), ALLEXCEPT ( Table1, Table1[Region], Table1[District] ) ), CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Region], Table1[District] ) ) )
@Vvelarde That works too - But why do we need the District?
Margin 2 = DIVIDE ( CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Region] ) ) - CALCULATE ( SUM ( Table1[Cost] ), ALLEXCEPT ( Table1, Table1[Region] ) ), CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Region] ) ) )
EDIT: You can actually revise mine to only this as well
Margin_Region = VAR RegionSales = CALCULATE ( SUM ( Table1[Sales] ), ALLSELECTED(Table1[Month]) ) VAR RegionCost = CALCULATE ( SUM ( Table1[Cost] ), ALLSELECTED(Table1[Month]) ) RETURN DIVIDE ( RegionSales - RegionCost, RegionSales )
Yes, There is no need to add the district column the obtain the result.
Thanks for your observation.
@Anonymous Actually the more I test this the more I think you should go with this....
Margin_Region ALS = VAR RegionSales = CALCULATE ( SUM ( Table1[Sales] ), ALLSELECTED(Table1[Month]) ) VAR RegionCost = CALCULATE ( SUM ( Table1[Cost] ), ALLSELECTED(Table1[Month]) ) RETURN DIVIDE ( RegionSales - RegionCost, RegionSales )
Look the picture - you can test this with a matrix visualization - I think the above will give you what you are looking for
If you use Slicers andd a Month Slicer and select only a number of months this will adjust
@Anonymous Add ALLSELECTED(Table1) like this...
Margin_Region 2 = VAR RegionSales = CALCULATE ( SUM ( Table1[Sales] ), VALUES ( Table1[Region] ), ALLSELECTED(Table1) ) VAR RegionCost = CALCULATE ( SUM ( Table1[Cost] ), VALUES ( Table1[Region] ), ALLSELECTED(Table1) ) RETURN DIVIDE ( RegionSales - RegionCost, RegionSales )
In my mind you have two separate things here. 1 is not having the margin info and 2 not having the row level security setup. Make another table related to the department ID. On this table put in the required margins per department. Make another table of user ID's and relate to the departament table on department ID.
The User id's can then be used in the row level security. DAX for RSL will be something like
=USERS[user_ID] = USERNAME()
you now have the margins by which can be bought back based on the selected department and the department is selected by login or in the case of people with multiple areas by slicer.
//If this is a solution please mark as such
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |