cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rynoh17 Helper IV
Helper IV

Calculated Measure for Yearly Cumulative % Across Months in a visual

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

3 ACCEPTED SOLUTIONS

Accepted Solutions
Vvelarde Community Champion
Community Champion

Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

@rynoh17

 

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] )
    )
)



Lima - Peru

View solution in original post

Sean Community Champion
Community Champion

Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

@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 )

 

View solution in original post

Sean Community Champion
Community Champion

Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

@rynoh17 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

 

% Margin per Region ALLSELECTED.png

View solution in original post

14 REPLIES 14
samdthompson Impactful Individual
Impactful Individual

Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

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

Microsoft
Microsoft

Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

@rynoh17

 

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

rynoh17 Helper IV
Helper IV

Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

@samdthompson

 

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).

samdthompson Impactful Individual
Impactful Individual

Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

@rynoh17 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()))

 

 

rynoh17 Helper IV
Helper IV

Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

@samdthompson

 

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.

Microsoft
Microsoft

Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

@rynoh17

 

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.

 

Calculated Measure for Yearly Cumulative % Across Months in a visual_1.jpg

 

Best Regards,

Herbert

rynoh17 Helper IV
Helper IV

Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

@v-haibl-msft @samdthompson

 

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?

 

https://www.dropbox.com/s/jxzfbfqjm5z3y2i/Sample.pbix?dl=0

Microsoft
Microsoft

Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

@rynoh17

 

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 )

Calculated Measure for Yearly Cumulative % Across Months in a visual_1.jpg

 

Best Regards,

Herbert

rynoh17 Helper IV
Helper IV

Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

@v-haibl-msft

 

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.

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors