Re: Modify existing calculations for Bar Charts an...

harshagraj · ‎10-23-2024

Hello all, i need help wrt to this post of myself. I got a solution from @Anonymous (thanks for that). But unfortunately this is working only i have a context. The current solution is working for matrix & table views only. If i apply the calculation on Bar Chart or any other visuals its going null/blank.
https://community.fabric.microsoft.com/t5/Desktop/Difference-in-Meter-Reading-Calculation-based-on-dates/m-p/4237950#M1332051

How can i modify the current calculation to work for visuals.

Difference = 
VAR _shifttype =
    SELECTEDVALUE ( 'Table'[ShiftStartOrEnd] )
VAR _date =
    SELECTEDVALUE ( 'Table'[ReadingCaptureOn] )
VAR _shift =
    SELECTEDVALUE ( 'Table'[Shift Name] )
VAR _input =
    SELECTEDVALUE ( 'Table'[Input] )
VAR _ss1 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Shift Name] = "Shift 1"&&'Table'[Input]=_input&&'Table'[ReadingCaptureOn]=_date )
    )
VAR _ss2 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED('Table'), 'Table'[Shift Name] = "Shift 2"&&'Table'[Input]=_input&&'Table'[ReadingCaptureOn]=_date )
    )
VAR _ss3 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED('Table'), 'Table'[Shift Name] = "Shift 3" &&'Table'[Input]=_input&&'Table'[ReadingCaptureOn]=_date )
    )
VAR _es1 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Shift Name] = "Shift 1"
                && 'Table'[Input] = _input
                && 'Table'[ReadingCaptureOn] = _date + 1
        )
    )
VAR _es2 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Shift Name] = "Shift 2"
                && 'Table'[Input] = _input
                && 'Table'[ReadingCaptureOn] = _date + 1
        )
    )
VAR _es3 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Shift Name] = "Shift 3"
                && 'Table'[Input] = _input
                && 'Table'[ReadingCaptureOn] = _date + 1
        )
    )
RETURN   
    SWITCH (
        TRUE (),
        _shifttype = "Start"
            && _shift = "Shift 1", _ss1 - _ss2,
        _shifttype = "Start"
            && _shift = "Shift 2", _ss2 - _ss3,
        _shifttype = "Start"
            && _shift = "Shift 3", _ss3 - _es1,
        _shifttype = "End"
            && _shift = "Shift 1", _ss3 - _es1,
        _shifttype = "End"
            && _shift = "Shift 2", _es1 - _es2,
        _shifttype = "End"
            && _shift = "Shift 3", _es2 - _es3
    )

@amitchandak @Greg_Deckler @Anonymous

saud968 · ‎10-23-2024

Try the below measure

Difference =
VAR _shifttype = SELECTEDVALUE('Table'[ShiftStartOrEnd])
VAR _date = SELECTEDVALUE('Table'[ReadingCaptureOn])
VAR _shift = SELECTEDVALUE('Table'[Shift Name])
VAR _input = SELECTEDVALUE('Table'[Input])

VAR _ss1 =
CALCULATE(
SUMX(
VALUES('Table'[ReadingCaptureOn]),
CALCULATE(SUM('Table'[Value]), 'Table'[Shift Name] = "Shift 1" && 'Table'[Input] = _input)
),
'Table'[ReadingCaptureOn] = _date
)

VAR _ss2 =
CALCULATE(
SUMX(
VALUES('Table'[ReadingCaptureOn]),
CALCULATE(SUM('Table'[Value]), 'Table'[Shift Name] = "Shift 2" && 'Table'[Input] = _input)
),
'Table'[ReadingCaptureOn] = _date
)

VAR _ss3 =
CALCULATE(
SUMX(
VALUES('Table'[ReadingCaptureOn]),
CALCULATE(SUM('Table'[Value]), 'Table'[Shift Name] = "Shift 3" && 'Table'[Input] = _input)
),
'Table'[ReadingCaptureOn] = _date
)

VAR _es1 =
CALCULATE(
SUMX(
VALUES('Table'[ReadingCaptureOn]),
CALCULATE(SUM('Table'[Value]), 'Table'[Shift Name] = "Shift 1" && 'Table'[Input] = _input)
),
'Table'[ReadingCaptureOn] = _date + 1
)

VAR _es2 =
CALCULATE(
SUMX(
VALUES('Table'[ReadingCaptureOn]),
CALCULATE(SUM('Table'[Value]), 'Table'[Shift Name] = "Shift 2" && 'Table'[Input] = _input)
),
'Table'[ReadingCaptureOn] = _date + 1
)

VAR _es3 =
CALCULATE(
SUMX(
VALUES('Table'[ReadingCaptureOn]),
CALCULATE(SUM('Table'[Value]), 'Table'[Shift Name] = "Shift 3" && 'Table'[Input] = _input)
),
'Table'[ReadingCaptureOn] = _date + 1
)

RETURN
SWITCH(
TRUE(),
_shifttype = "Start" && _shift = "Shift 1", _ss1 - _ss2,
_shifttype = "Start" && _shift = "Shift 2", _ss2 - _ss3,
_shifttype = "Start" && _shift = "Shift 3", _ss3 - _es1,
_shifttype = "End" && _shift = "Shift 1", _ss3 - _es1,
_shifttype = "End" && _shift = "Shift 2", _es1 - _es2,
_shifttype = "End" && _shift = "Shift 3", _es2 - _es3
)

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

harshagraj · ‎10-23-2024

Hi @saud968 thanks for the solution, but it doesnt work
Before

After

When i remove Shift it should aggregate or should display totals but its going blank.

saud968 · ‎10-23-2024

Here’s an updated version of your DAX formula that includes a check for when the ‘Shift’ context is not present:

Difference =
VAR _shifttype = SELECTEDVALUE('Table'[ShiftStartOrEnd])
VAR _date = SELECTEDVALUE('Table'[ReadingCaptureOn])
VAR _input = SELECTEDVALUE('Table'[Input])
VAR _shift = SELECTEDVALUE('Table'[Shift Name], "All Shifts")

VAR _ss1 =
CALCULATE(
SUM('Table'[Value]),
'Table'[Shift Name] = "Shift 1" && 'Table'[Input] = _input && 'Table'[ReadingCaptureOn] = _date
)

VAR _ss2 =
CALCULATE(
SUM('Table'[Value]),
'Table'[Shift Name] = "Shift 2" && 'Table'[Input] = _input && 'Table'[ReadingCaptureOn] = _date
)

VAR _ss3 =
CALCULATE(
SUM('Table'[Value]),
'Table'[Shift Name] = "Shift 3" && 'Table'[Input] = _input && 'Table'[ReadingCaptureOn] = _date
)

VAR _es1 =
CALCULATE(
SUM('Table'[Value]),
'Table'[Shift Name] = "Shift 1" && 'Table'[Input] = _input && 'Table'[ReadingCaptureOn] = _date + 1
)

VAR _es2 =
CALCULATE(
SUM('Table'[Value]),
'Table'[Shift Name] = "Shift 2" && 'Table'[Input] = _input && 'Table'[ReadingCaptureOn] = _date + 1
)

VAR _es3 =
CALCULATE(
SUM('Table'[Value]),
'Table'[Shift Name] = "Shift 3" && 'Table'[Input] = _input && 'Table'[ReadingCaptureOn] = _date + 1
)

RETURN
IF(
_shift = "All Shifts",
SUMX(
VALUES('Table'[Shift Name]),
SWITCH(
TRUE(),
_shifttype = "Start", _ss1 - _ss2 + _ss2 - _ss3 + _ss3 - _es1,
_shifttype = "End", _ss3 - _es1 + _es1 - _es2 + _es2 - _es3
)
),
SWITCH(
TRUE(),
_shifttype = "Start" && _shift = "Shift 1", _ss1 - _ss2,
_shifttype = "Start" && _shift = "Shift 2", _ss2 - _ss3,
_shifttype = "Start" && _shift = "Shift 3", _ss3 - _es1,
_shifttype = "End" && _shift = "Shift 1", _ss3 - _es1,
_shifttype = "End" && _shift = "Shift 2", _es1 - _es2,
_shifttype = "End" && _shift = "Shift 3", _es2 - _es3
)
)

This formula includes a check for when the ‘Shift’ context is not present (_shift = "All Shifts"), and it aggregates the values accordingly.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

harshagraj · ‎10-25-2024

HI @saud968 thanks for providing the solution. Unfortunately the totals are not correct. And it is not just the shift i need i might need for Shiftinput also. How can we make different approach for this. Please guide me.

saud968 · ‎10-25-2024

Hopefully this will workout for you

Difference =
VAR SummaryTable =
SUMMARIZE(
'Table',
'Table'[ReadingCaptureOn],
'Table'[Shift Name],
'Table'[ShiftStartOrEnd],
'Table'[Input],
"TotalValue", SUM('Table'[Value])
)

VAR _shifttype = SELECTEDVALUE('Table'[ShiftStartOrEnd])
VAR _date = SELECTEDVALUE('Table'[ReadingCaptureOn])
VAR _input = SELECTEDVALUE('Table'[Input])
VAR _shift = SELECTEDVALUE('Table'[Shift Name], "All Shifts")

VAR _ss1 =
CALCULATE(
SUMX(
FILTER(SummaryTable, [Shift Name] = "Shift 1" && [Input] = _input && [ReadingCaptureOn] = _date),
[TotalValue]
)
)

VAR _ss2 =
CALCULATE(
SUMX(
FILTER(SummaryTable, [Shift Name] = "Shift 2" && [Input] = _input && [ReadingCaptureOn] = _date),
[TotalValue]
)
)

VAR _ss3 =
CALCULATE(
SUMX(
FILTER(SummaryTable, [Shift Name] = "Shift 3" && [Input] = _input && [ReadingCaptureOn] = _date),
[TotalValue]
)
)

VAR _es1 =
CALCULATE(
SUMX(
FILTER(SummaryTable, [Shift Name] = "Shift 1" && [Input] = _input && [ReadingCaptureOn] = _date + 1),
[TotalValue]
)
)

VAR _es2 =
CALCULATE(
SUMX(
FILTER(SummaryTable, [Shift Name] = "Shift 2" && [Input] = _input && [ReadingCaptureOn] = _date + 1),
[TotalValue]
)
)

VAR _es3 =
CALCULATE(
SUMX(
FILTER(SummaryTable, [Shift Name] = "Shift 3" && [Input] = _input && [ReadingCaptureOn] = _date + 1),
[TotalValue]
)
)

RETURN
IF(
_shift = "All Shifts",
SUMX(
VALUES('Table'[Shift Name]),
SWITCH(
TRUE(),
_shifttype = "Start", _ss1 - _ss2 + _ss2 - _ss3 + _ss3 - _es1,
_shifttype = "End", _ss3 - _es1 + _es1 - _es2 + _es2 - _es3
)
),
SWITCH(
TRUE(),
_shifttype = "Start" && _shift = "Shift 1", _ss1 - _ss2,
_shifttype = "Start" && _shift = "Shift 2", _ss2 - _ss3,
_shifttype = "Start" && _shift = "Shift 3", _ss3 - _es1,
_shifttype = "End" && _shift = "Shift 1", _ss3 - _es1,
_shifttype = "End" && _shift = "Shift 2", _es1 - _es2,
_shifttype = "End" && _shift = "Shift 3", _es2 - _es3
)
)

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!