Measure Help

[RanjanThammaiah]

Hi All,

Can someone help me in correcting the below formula(received from Amith)? Where there is no relationship between the table.

Measure = CALCULATE([TVPS],

FILTER('S&P',

'S&P'[SubServiceLineGroupingDesc]in VALUES('Comparision SSL'[SSL])))

basically when I cross filter from Adv Solution on first slicer and Audit on the second slicer. I should be getting the count value is 14,471,559. but there some problems with the formula where I cannot fix to get the correct value.

Sample Report

[amitchandak]

@RanjanThammaiah , for Adv Solution you do not have Audit Data. Both slicer are filtering then there is not data

[RanjanThammaiah]

@amitchandak Yes, I don't have any data. The table(Comparision SSL) I have created to get the intersecting count.(Without that table I can't get the Cross-selling/Count by SSL)

For Eg.

Cross-selling formula that I have used to get the count is.

Cross Selling =

VAR InitialPurchase =VALUES('S&P'[UDN])

VAR ComparisionPurchase =CALCULATETABLE(VALUES('S&P'[UDN]),

ALLSELECTED(SSL),

TREATAS(VALUES('Comparision SSL'[SSL]),'S&P'[SubServiceLineGroupingDesc]))

Return

IF(SELECTEDVALUE(SSL[SSL])=SELECTEDVALUE('Comparision SSL'[SSL- Copy]),

BLANK(),

COUNTROWS(INTERSECT( InitialPurchase, ComparisionPurchase)))

This is what I was working on to get the count(Cross-selling) and I was looking to get the total value for each intersecting count.

[AllisonKennedy]

Can you provide links to other posts that you've made relating to this topic?

[RanjanThammaiah]

Original Post

[AllisonKennedy]

This clarifies your question a little bit, but I'm still not sure where the 14,471,559 value should be coming from? Something like this might get you closer: Measure = SUMX(
FILTER(ALL('S&P'),
'S&P'[SubServiceLineGroupingDesc]in VALUES('Comparision SSL'[SSL])),'S&P'[TotalValueOfPotentialSale])

But I don't know what this number is trying to show, so not sure what additional filters need to be placed on it.

[RanjanThammaiah]

Hi @AllisonKennedy 

this started giving the total of Audit Value.

In the below SS hope you can make the difference.

Whenever I cross filter from both the slicer I should be getting the count and the value.

Sample Report

The value I am getting is the Total value of the Audit. can see the same value bit the count changes.