Need Help

[Vindesh]

Hi,

I have 3 tables (PrimarySales,SecondarySales and Hierarchy).

Territory Column is common among them and i have joined Hierarchy table with PrimarySales and Secondary sales table on common Key(Territory).

Now, I want to have a mesure which can give me count of Territory whose Secondary sales is <90% of Primary Sales or vice versa.

Please guide and help to achieve the same.

[PaulDBrown]

Can you show a depiction of how the model is set up please?

[Vindesh]

[PaulDBrown]

Ok, you need a couple of measures (+ the sum for the sales in each table)

Sales 1 where S2 > 90% =
VAR _90Threshold =
    DIVIDE ( [Sum Sales 1], [Sum Sales 2] )
RETURN
    IF ( _90Threshold > 0.9, 1 )

Final = 
SUMX(VALUES('Hierarchy'[Region]), [Sales 1 where S2 > 90%])

[Vindesh]

Hi,

Many thanks for your reply ans yes same objective even i want to achieve which i rflecting in your image.However,request you to explain bit in detail for measure creation part.

[PaulDBrown]

The first measure, [

Sales 1 where S2 > 90%

Returns the value 1 when the division of the primary sales by the secondary sales is greater than 0.9 (90%)

The second measure,

Final

Sums these 1 values to obtain the total.

I've attached the sample PBIX file for you

[Vindesh]

Hi 

Many thanks, this has worked for me.

However, one challange is it is giving me count which include duplicates territory names since my hierarchy table contains duplicates.

Not able to figure out to hoe to get distinct count in sumx.

Please guide/help

[PaulDBrown]

I'm not sure what you mean. The relationships stemming from your Hirarchy table are on the "One" side, which means they are unique values. There should be no duplicates.

[Vindesh]

Hierarchy table contains duplicates of territory name since its on brand level.

If any territory is bound to sell more than one brand than for that territory there is more than one record in hierarchy table.So i ahve modified your dax in to below one

FInal Count = CALCULATE(DISTINCTCOUNT(Master[Territory-22]),FILTER(Master,'Measure'[90% SS])).

However this is also not working for all the records correctly because its considering those records also where either one of the conditons is met.

90% SS =

VAR _90Threshold = DIVIDE([Total SS],[Total PS])

VAR _90ThresholdAch = DIVIDE([Total PS],[Total Tgt])

RETURN

IF(AND(_90Threshold > 0.9,_90ThresholdAch > 0.9),1)

In below image you can see for siliguri also it is giving me a count.

[PaulDBrown]

Please share some non-confidential sample data or PBIX file

[amitchandak]

@Vindesh ,  Try measure like

Sumx(Filter(values(Hierarchy[Territory]), [Secondary Sales measure] >90 ) , [Secondary Sales measure])