DAX Help

 for this error 

CrossSell_Final_Fixed = 

VAR _selectedClient = SELECTEDVALUE( 'ClientSlicerTable'[Prospect Client Name] )

VAR _currOffering = SELECTEDVALUE( 'Pipeline'[Service Offering] )

VAR _rowClient = SELECTEDVALUE( 'Pipeline'[Prospect Client Name] )

-- 1. Get the Sector of the client selected in the slicer

VAR _selectedSector = 

    CALCULATE(

        MAX( 'Pipeline'[Client Sector] ),

        REMOVEFILTERS( 'Pipeline' ),

        'Pipeline'[Prospect Client Name] = _selectedClient

    )

-- 2. Get the Sector of the Peer on the current row

VAR _rowSector = MAX( 'Pipeline'[Client Sector] )

-- 3. Check if the Sliced Client has this offering

VAR _selectedHasOffer = 

    CALCULATE(

        COUNTROWS( 'Pipeline' ),

        REMOVEFILTERS( 'Pipeline' ),

        'Pipeline'[Prospect Client Name] = _selectedClient,

        'Pipeline'[Service Offering] = _currOffering

    )

-- 4. Get Actual Sales for this peer

VAR _actualSales = [sales]

-- 5. Final Logic

RETURN

IF( _rowClient = _selectedClient || _rowSector <> _selectedSector, 

    BLANK(),

    IF( _selectedHasOffer > 0,

        COALESCE( _actualSales, 0 ), -- COALESCE fills the g

ap with 0

        BLANK()

    )

)

Why this fixes the error:

Removed FORMAT(): By removing the string conversion, we eliminate the "out of range for format string" error.

COALESCE(..., 0): This is the key to "filling the gap." It ensures that even if a peer has no sales, the measure returns a 0, forcing Power BI to display the row and column context.

Final Configuration Steps

To make the 0s look like currency and ensure the gaps stay visible:

Format as Currency: Select your new measure in the Data pane. Go to Measure tools at the top and set the Format to Currency. This way, it stays a number but looks like money.

Force the Grid: * In your Matrix visual, click the down-arrow on Prospect Client Name (in Rows) and select "Show items with no data."

Do the same for Service Offering (in Columns).

Conditional Formatting (Optional): To make the "Gaps" (the 0s) stand out, go to Format visual > Cell elements, turn on Background color for this measure, and set a rule: If value is 0, then color is Light Orange.