Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
11 months ago
Solved

Measure to Calculated column

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
Need Help
  • DataNinja777's avatar
    DataNinja777
    11 months ago

    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,

3 Replies

  • techies's avatar
    techies
    Super User
    11 months ago

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

     

     

     

  • DataNinja777's avatar
    DataNinja777
    Super User
    11 months ago

    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,