DAX Help

[vjnvinod]

Dear Experts,

I'm building a Power BI matrix that compares peer clients against a selected client's service offerings. The goal is to identify which peers have or don’t have the same offerings as the selected client, and flag those gaps as potential cross-sell opportunities

Data Model:

• Pipeline table with columns: Prospect Client Name, Service Offering, Total Value Of Potential Sale.
• ClientSlicerTable (disconnected from the Pipeline table) used to select a single client.)
• Peer clients are filtered using a PeerFilter measure (same sector, excluding selected client) which is set to 1 based on the below measure
  PeerFilter =

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

  VAR _selectedSector = CALCULATE(

      MAX( Pipeline[Client Sector] ),

      ALL( Pipeline ),

      Pipeline[Prospect Client Name] = _selectedClient

  )

  VAR _rowSector = MAX( Pipeline[Client Sector] )

  VAR _rowClient = MAX( Pipeline[Prospect Client Name] )

  RETURN

      IF( NOT ISBLANK(_selectedClient) &&

          _rowSector = _selectedSector &&

          _rowClient <> _selectedClient,

          1,

          0

      )
  I need to display the potential cross-selling value for peer clients who currently have no sales (blank or 0) for service offerings that the selected client already has. The goal is to identify where peers are missing offerings that the selected client provides, and estimate the potential value they could generate if they adopted those offerings
• to do that i have used the below measure 
  CrossSellSales_Final =

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

  VAR _currOffering   = SELECTEDVALUE( Pipeline[Service Offering] )

  VAR _selectedHasOffer =

      CALCULATE(

          COUNTROWS( Pipeline ),

          REMOVEFILTERS( Pipeline[Prospect Client Name] ),       -- ignore current peer filter

          Pipeline[Prospect Client Name] = _selectedClient,

          Pipeline[Service Offering]    = _currOffering

      )

  VAR _peerSales = [sales]

  VAR _baseResult =

      IF( ISBLANK( _selectedClient ), BLANK(),

          IF( _selectedHasOffer = 0, BLANK(),

              IF( NOT ISBLANK( _peerSales ), _peerSales, 0 )

          )

      )

  RETURN

      _baseResult,  this measure doesn't give me what i am looking for

  

  in the above example Financial statemnt audit and Financial Statement review are the sales offerings for ANB capital but in peer Client table, i only want to see, if peer client doesn't have both or one of the offerings, their potential sales value should be picked from the first table of Selected clients.

  I hope i was able to explain the requirement, might be a small dax fix in my current dax measure, would be great to get some support

[v-echaithra]

Hi @vjnvinod ,

I just wanted to check if the issue has been resolved on your end, or if you require any further assistance, please provide the sample Pbix file. How to provide sample data in the Power BI Forum

You can refer the following link to upload the file to the community.
How to upload PBI in Community

We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[Ashish_Mathur]

Hi,

Could you share some dummy data set to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[HarishKM]

@vjnvinod Hey,
I am taking some assumption for your requirement and creating dax based on that.

Assumptions

• [Sales] = SUM of Pipeline[Total Value Of Potential Sale].
• Matrix: Rows = Pipeline[Prospect Client Name], Columns = Pipeline[Service Offering].
• Visual-level filter: PeerFilter = 1 (your existing measure).

Key measure (show selected client’s value when peer lacks the offering)
CrossSellPotential =
VAR SelectedClient = SELECTEDVALUE(ClientSlicerTable[Prospect Client Name])
VAR RowClient = MAX(Pipeline[Prospect Client Name])
VAR CurrOffering = SELECTEDVALUE(Pipeline[Service Offering])
VAR SelValue =
CALCULATE(
[Sales],
REMOVEFILTERS(Pipeline[Prospect Client Name]),
Pipeline[Prospect Client Name] = SelectedClient,
Pipeline[Service Offering] = CurrOffering
)
VAR PeerValue = [Sales]
RETURN
IF(
ISBLANK(SelectedClient) || RowClient = SelectedClient,
BLANK(),
IF( SelValue > 0 && (ISBLANK(PeerValue) || PeerValue = 0),
SelValue, -- potential = selected client’s value
BLANK() -- hide if peer already has the offering or selected lacks it
)
)

Optional flag (to filter/highlight only gaps)
CrossSellFlag =
VAR SelectedClient = SELECTEDVALUE(ClientSlicerTable[Prospect Client Name])
VAR CurrOffering = SELECTEDVALUE(Pipeline[Service Offering])
VAR SelHas =
CALCULATE(
[Sales] <> 0,
REMOVEFILTERS(Pipeline[Prospect Client Name]),
Pipeline[Prospect Client Name] = SelectedClient,
Pipeline[Service Offering] = CurrOffering
)
VAR PeerHas = NOT ISBLANK([Sales]) && [Sales] <> 0
RETURN IF(SelHas && NOT PeerHas, 1, 0)

How to use

• Put CrossSellPotential in Values.
• Keep PeerFilter = 1 on visual to show only peers.
• Optionally add a visual-level filter CrossSellFlag = 1 to show only gap cells.

Few more suggestion- 

• If your [Sales] can have duplicates per offering, SelValue sums them; switch to MAX/AVERAGEX if needed.
• Ensure the client slicer is single-select; otherwise wrap logic with HASONEVALUE.

Thanks

Haish K 

If I resolve your issue. Kindly give kudos to this post and accept it as a solution so other can refer this.

[grazitti_sapna]

Hi @vjnvinod,

It seems the issue is with the logic you are implementing,

Try below DAXs to fix this issue

Sales :=
SUM ( Pipeline[Total Value Of Potential Sale] )

Selecting Client’s Sales for Current Offering

SelectedClient_Offering_Sales :=
VAR _selectedClient =
SELECTEDVALUE ( ClientSlicerTable[Prospect Client Name] )
VAR _currOffering =
SELECTEDVALUE ( Pipeline[Service Offering] )
RETURN
CALCULATE (
[Sales],
REMOVEFILTERS ( Pipeline[Prospect Client Name] ),
Pipeline[Prospect Client Name] = _selectedClient,
Pipeline[Service Offering] = _currOffering
)

Finally calculate cross sell 

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

VAR _peerSales =
[Sales]

VAR _selectedClientSales =
[SelectedClient_Offering_Sales]

RETURN
IF (
ISBLANK ( _selectedClient ),
BLANK(),

-- Selected client does NOT have this offering → hide row
IF (
ISBLANK ( _selectedClientSales ) || _selectedClientSales = 0,
BLANK(),

-- Peer HAS the offering → show actual peer sales
IF (
NOT ISBLANK ( _peerSales ) && _peerSales > 0,
_peerSales,

-- Peer does NOT have it → show potential from selected client
_selectedClientSales
)
)
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[vjnvinod]

@grazitti_sapna 

thank you, but unfortunately dax still doesn't work, for example in the below view for Peer client Abhudhabi Global market  doesn't have sales in RI-Financial Crimes, so it should pick up the value from selected clients sales value from RI-Financial crimes, in this case $45K, so this $45K becomes potential cross selling opportunity?

Not sure if i am able to explain this

[amitchandak]

@vjnvinod , Have two measures like below, use the second measure M2 with ClientSlicerTable or pipeline Prospect Client Name

M1 =
VAR _selectedClient = SELECTEDVALUE( ClientSlicerTable[Prospect Client Name] )
_tab = summarize(filter(Pipeline, Pipeline[Prospect Client Name] = _selectedClient ), Pipeline[Client Sector] )
return
Countrows(filter(Pipeline, Pipeline[Client Sector] in _tab))

M2 = Countx(values(ClientSlicerTable[Prospect Client Name]) , If(ISBLANK([M1]),[Prospect Client Name], blank()))

or

M2 = Countx(values(Pipeline[Prospect Client Name] ) , If(ISBLANK([M1]),[Prospect Client Name], blank()))

If both tables are joined first one should work. Else try second M1

[vjnvinod]

Looks like there is some logical issue, unfortunately even co pilot is not putting the right solution

M1 measure

M1 =
VAR _selectedClient = SELECTEDVALUE( ClientSlicerTable[Prospect Client Name] )
_tab = summarize(filter(Pipeline, Pipeline[Prospect Client Name] = _selectedClient ), Pipeline[Client Sector] )
return
Countrows(filter(Pipeline, Pipeline[Client Sector] in _tab))

The syntax for '_tab' is incorrect. (DAX(VAR _selectedClient = SELECTEDVALUE( ClientSlicerTable[Prospect Client Name] )_tab = summarize(filter(Pipeline, Pipeline[Prospect Client Name] = _selectedClient ), Pipeline[Client Sector] )returnCountrows(filter(Pipeline, Pipeline[Client Sector] in _tab)))).

what copilot is giving is

M1 =

VAR _selectedClient =

    SELECTEDVALUE ( ClientSlicerTable[Prospect Client Name] )

VAR _tab =

    CALCULATETABLE (

        VALUES ( Pipeline[Client Sector] ),

        FILTER ( Pipeline, Pipeline[Prospect Client Name] = _selectedClient )

    )

RETURN

COUNTROWS (

    FILTER ( Pipeline, Pipeline[Client Sector] IN _tab )

)

@amitchandak