Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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



Lima - Peru

View solution in original post

Sean
Community Champion
Community Champion

@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

@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

 

% Margin per Region ALLSELECTED.png

View solution in original post

14 REPLIES 14
v-haibl-msft
Employee
Employee

@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

Anonymous
Not applicable

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

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

 

 

// if this is a solution please mark as such. Kudos always appreciated.
Anonymous
Not applicable

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

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

 

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

 

Best Regards,

Herbert

Anonymous
Not applicable

@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

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

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

 

Best Regards,

Herbert

Anonymous
Not applicable

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

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



Lima - Peru
Sean
Community Champion
Community Champion

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

 

Vvelarde
Community Champion
Community Champion

@Sean

 

Yes, There is no need to add the district column the obtain the result.

 

Thanks for your observation.




Lima - Peru
Sean
Community Champion
Community Champion

@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

 

% Margin per Region ALLSELECTED.png

Sean
Community Champion
Community Champion

@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 )
samdthompson
Memorable Member
Memorable Member

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

// if this is a solution please mark as such. Kudos always appreciated.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.