Measure to Calculated column

[AllanBerces]

Hi can anyon help me to change the measure in to calculated column

TRADE_Count =

VAR _Day = SELECTEDVALUE( 'Table01'DATE] )

VAR _Loc = SELECTEDVALUE( 'Table01'[Code] )

RETURN

CALCULATE(

    COUNTROWS( 'Table01' ),

    'Table01'DATE] = _Day,

    'Table01'[Code] = _Loc,

    REMOVEFILTERS()

)

and this one

TRADE_Max Count/Week =

VAR _Week = SELECTEDVALUE( 'Table01'[Week No:] )

VAR _Loc = SELECTEDVALUE( 'Table01'[Code] )

VAR _Table =

ADDCOLUMNS(

    CALCULATETABLE(

        SUMMARIZE( 'Table01', 'Table01'[DATE], 'Table01'[Code], 'Table01'[Week No:] ),

        REMOVEFILTERS('Table01'[DPR_DATE], 'Table01'[TRADES] ),

        'Table01'[Week No:] = _Week, 'Table01'[Code] = _Loc

    ),

    "@count", CALCULATE( [TRADE_Count], REMOVEFILTERS( 'Table01'[TRADES] ) )

)

RETURN

MAXX ( _Table, [@count] )

Thank you

[DataNinja777 · Accepted solution]

Hi @AllanBerces ,

To change your TRADE_Count measure into a calculated column, you need a formula that works row by row instead of reacting to report filters. The key is to replace SELECTEDVALUE with a direct reference to the current row's data. This new column will, for each row, count how many total rows in the table share that specific row's date and code. This effectively calculates the daily trade count for that location and stores it on each row.

TRADE_Count_Column =
VAR _currentDate = 'Table01'[DATE]
VAR _currentCode = 'Table01'[Code]
RETURN
    CALCULATE (
        COUNTROWS ( 'Table01' ),
        FILTER (
            ALL ( 'Table01' ),
            'Table01'[DATE] = _currentDate &&
            'Table01'[Code] = _currentCode
        )
    )

Next, to convert the TRADE_Max Count/Week measure, you'll create a second calculated column that uses the first one you just made. For each row, this formula will find the highest daily count from your new TRADE_Count_Column that occurred within that row's specific week number and code. It scans the whole table, groups the data by week and code in the background, and then returns the peak daily value for that group.

TRADE_Max_Count_Week_Column =
VAR _currentWeek = 'Table01'[Week No:]
VAR _currentCode = 'Table01'[Code]
RETURN
    CALCULATE (
        MAX ( 'Table01'[TRADE_Count_Column] ),
        FILTER (
            ALL ( 'Table01' ),
            'Table01'[Week No:] = _currentWeek &&
            'Table01'[Code] = _currentCode
        )
    )

Best regards,

[AllanBerces]

Hi @DataNinja777 @techies thank you very much working as i need

[techies]

Hi @AllanBerces this is from your earlier post- sharing the calculated columns , please try

TRADE_Max Count/Week column =

CALCULATE (

    MAX ( Sheet8[Count] ),

    ALLEXCEPT (

        Sheet8,

        Sheet8[Trade],

        Sheet8[week num]

    )

)

Total Trades/Week column =

VAR vWeek = Sheet8[week num]

RETURN

CALCULATE (

    SUMX (

        VALUES ( Sheet8[Trade] ),

        CALCULATE (

            MAX ( Sheet8[Count] ),

            ALLEXCEPT ( Sheet8, Sheet8[Trade],  Sheet8[week num] )

        )

    ),

    ALLEXCEPT ( Sheet8,  Sheet8[week num] )

)