Forum Discussion

Regular Visitor

4 years ago

Need Help

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(Te...

Need Help

PaulDBrown

Community Champion

4 years ago

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

90% Threshold.pbix32 KB

Vindesh

Regular Visitor

4 years ago

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
Community Champion
4 years ago
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
  Regular Visitor
  4 years ago
  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
    Community Champion
    4 years ago
    Please share some non-confidential sample data or PBIX file