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

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