Sum of entire column to show in each row

[jcastr02]

I have a calculated table in Power BI below

AggregatedData - with Seasonal Volume = SUMMARIZE('Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View',
        'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[Receiving Store],
        'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[Reporting Week],
        'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[Final Store Closure Date],
        'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[Store #],
        'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[Change in Status],
        'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[DE Allowed],
        'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[DR Allowed],
        'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[PCC Allowed],
        'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[DE Current Support],
        'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[DR Current Support],
        'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[PCC Current Support],
        'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[SeasonalVolume.Avg Rx Volume],
"TotalBaseRx",MAX('Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[SeasonalVolume.Avg Rx Volume]),
"TotalRetainedRx", SUM('Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[uRx/Day - Retained Scripts - Receiving Store]),
"NewRxVolume", MAX('Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[SeasonalVolume.Avg Rx Volume])+SUM('Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[uRx/Day - Retained Scripts - Receiving Store]))

These results are produced when filtering for Receiving store 11365

Receiving Store	Reporting Week	Final Store Closure Date	Store #	Change in Status	DE Allowed	DR Allowed	PCC Allowed	DE Current Support	DR Current Support	PCC Current Support	SeasonalVolume.Avg Rx Volume	TotalBaseRx	TotalRetainedRx	NewRxVolume	NewRxVolumeCategory	RetainedRxVolumeCatgory	Category of Support
11365	2/12/2025 0:00	2/27/2025 0:00	2575	Updated	Y	Y	Y		AutoPhLEX + On Demand		219.5	219.5	4	223.5	100+	1+	No Support
11365	2/12/2025 0:00	2/25/2025 0:00	3171	Updated	Y	Y	Y				219.5	219.5	42	261.5	250+	25+	Temp PhLEX

How could I get the New Rx Volume formula to show 265.5 in each row.  It would sum up the TotalbaseRx + sum of each item in TotalretainedRx  (219.5+4+42)

[Anonymous]

Hi ,

Based on the information, try using the following DAX formula for NewRxVolumn.

"NewRxVolume",
   MAX('Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[SeasonalVolume.Avg Rx Volume])
   + CALCULATE(
    SUM('Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[uRx/Day - Retained Scripts - Receiving Store]),
    ALL('Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View')
   )

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@jcastr02 

Could you try this version:

AggregatedData =
SUMMARIZE (
    'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View',
    'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[Receiving Store],
    'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[Reporting Week],
    'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[Final Store Closure Date],
    'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[Store #],
    'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[Change in Status],
    'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[DE Allowed],
    'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[DR Allowed],
    'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[PCC Allowed],
    'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[DE Current Support],
    'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[DR Current Support],
    'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[PCC Current Support],
    'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[SeasonalVolume.Avg Rx Volume],
    "TotalBaseRx",
        MAX ( 'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[SeasonalVolume.Avg Rx Volume] ),
    "TotalRetainedRx",
        SUM ( 'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[uRx/Day - Retained Scripts - Receiving Store] ),
    "NewRxVolume",
        VAR _TotalBaseRx =
            MAX ( 'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[SeasonalVolume.Avg Rx Volume] )
        VAR _TotalRetainedRx =
            SUMX (
                'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View',
                'Closing Stores - Recieving Volume - Store#1 & Roll Up - Matrix View'[uRx/Day - Retained Scripts - Receiving Store]
            )
        RETURN
            _TotalBaseRx + _TotalRetainedRx
)