matrix with subtotals and calculation

[Anonymous]

Hello!

I have data that I want to have in Power BI in a visal like this - Stores by employees and then subtotals by Stores

The data I have now is in a format like this

I have managed to get the number of total shifts, Morning shifts and Evening Shifts (by using a custom column) but what I don't get is the % of morning/evening shifts (simple calculation morning shifts/total shifts)

I have tried it in Excel Power Query and Pivot as well as in Power BI with measures, calculated fields and all sorts of other ways but never arrived to the result..

Can please anyone help to guide me in the right direction?

[Kedar_Pande · Accepted solution]

@Anonymous 

Create a calculated column:

Shift Type = 
IF(HOUR([Start of shift]) < 12, "Morning", "Evening")

Total Shifts Measure:

TotalShifts = COUNTROWS('YourTable')

Morning Shifts Measure:

MorningShifts = 
CALCULATE(
COUNTROWS('YourTable'),
'YourTable'[Shift Type] = "Morning"
)

Evening Shifts Measure

EveningShifts = 
CALCULATE(
COUNTROWS('YourTable'),
'YourTable'[Shift Type] = "Evening"
)

Morning Shift Percentage:

MorningShiftPercentage = 
DIVIDE([MorningShifts], [TotalShifts], 0) // Avoid divide by zero

Evening Shift Percentage

EveningShiftPercentage = 
DIVIDE([EveningShifts], [TotalShifts], 0) // Avoid divide by zero

If this helped, a Kudos 👍 or Solution mark would be great!🎉
Cheers,
Kedar Pande
Connect on LinkedIn

[Anonymous]

Super, thank you! this worked very well 🙂

Can you also maybe support if I want to add another measure/column that checks if the evening shifts % is below 25% or above 75% (in Excel it would be like this)