how to calculate a total for a given day of week from row grand total

[smjzahid]

I want to calculate total for a given day of the week out of the grand total, How do we do this in DAX or Power Query whichever is highly permormant.

and here is my dataset,

 for eg: for 25/04/2021

Bearings (Baseline) = 205/398 = 51.5% and so forth and so on. 

I want to divide the cell value for given day of week against the grand total of the Row 

Note: The column values (Dates) are correctly saved as Date datatype in Power BI

Here is the final output I want to get to

Below is my Data in Power BI

[v-luwang-msft · Accepted solution]

Hi @smjzahid ,

Try the following steps:

Step1,create the following measure:

dayall =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Type] = MAX ( 'Table'[Type] )
            && 'Table'[Baseline/Actual] = MAX ( 'Table'[Baseline/Actual] )
            && 'Table'[Date] <= MAX ( 'Table'[Date] )
    )
)

total =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Type] = MAX ( 'Table'[Type] )
            && 'Table'[Baseline/Actual] = "Baseline"
    )
)

per =
VAR TEST1 = [dayall] / [total]
VAR TEST2 =
    ROUND ( DIVIDE ( [dayall], [total], 0 ), 3 ) * 100 & "%"
VAR Total =
    IF ( HASONEVALUE ( 'Table'[Date] ), TEST2, [total] )
RETURN
    Total

Wish it is helpful for you!

Best Regards

Lucien

[amitchandak]

@smjzahid , Try a measure like

divide(sum(Table[Value]), calculate(sum(Table[Value]), filter(allselected(Table), Table[Type] = max(Table[Type]) && Table[baseline/Actual] = max(Table[baseline/Actual]))))

[smjzahid]

Hi @amitchandak 

Thanks for your proposed solution, I have tried as advised by yourself, However, I am not getting the percentage correct for the Bearing baseline and actual both should be  (51.5%). However, I am getting (72.70%) for 25/04/2021 for actual,

BFO Summary % = 

divide(sum('BFO Summary (Weekly)'[Value]), calculate(sum('BFO Summary (Weekly)'[Value]),
 filter(allselected('BFO Summary (Weekly)'), 'BFO Summary (Weekly)'[Type] 
= max('BFO Summary (Weekly)'[Type]) && 'BFO Summary (Weekly)'[Baseline/Actual] = max('BFO Summary (Weekly)'[Baseline/Actual]))))

[smjzahid]

Hi @amitchandak ,

I think we are missing something in our calculation, If you please closely monitor my PBI model, it shows the total of 205 for both (Baseline and Actual) for Bearings for the 25/04/2021, We are getting it correct for the Baseline but for Actual we are get it higher percentage (72%),

I think something to do with filter context, we are missing something.