topic Re: Need Help in Desktop

Need Help

Vindesh — Mon, 04 Jul 2022 10:22:21 GMT

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.

Re: Need Help

amitchandak — Mon, 04 Jul 2022 10:24:13 GMT

Vindesh , Try measure like

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

Re: Need Help

PaulDBrown — Mon, 04 Jul 2022 10:25:02 GMT

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

Re: Need Help

Vindesh — Mon, 04 Jul 2022 10:30:52 GMT

Re: Need Help

PaulDBrown — Mon, 04 Jul 2022 12:08:59 GMT

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%])

Re: Need Help

Vindesh — Mon, 04 Jul 2022 15:07:10 GMT

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.

Re: Need Help

PaulDBrown — Mon, 04 Jul 2022 16:34:21 GMT

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

Re: Need Help

Vindesh — Mon, 15 Aug 2022 06:31:29 GMT

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

Re: Need Help

PaulDBrown — Mon, 15 Aug 2022 10:34:46 GMT

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.

Re: Need Help

Vindesh — Mon, 15 Aug 2022 10:42:59 GMT

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.

Re: Need Help

PaulDBrown — Mon, 15 Aug 2022 11:13:52 GMT

Please share some non-confidential sample data or PBIX file