Forum Discussion

Vindesh's avatar
Vindesh
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's avatar
PaulDBrown
Community Champion
4 years ago

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's avatar
Vindesh
Regular Visitor
4 years ago

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's avatar
    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

     

    • Vindesh's avatar
      Vindesh
      Regular Visitor
      4 years ago

      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's avatar
        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.