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.

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

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

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

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

Need Help | Microsoft Fabric Community

10 Replies

amitchandak
Super User
4 years ago
Vindesh , Try measure like

Sumx(Filter(values(Hierarchy[Territory]), [Secondary Sales measure] >90 ) , [Secondary Sales measure])
PaulDBrown
Community Champion
4 years ago
Can you show a depiction of how the model is set up please?
- Vindesh
  Regular Visitor
  4 years ago
  - 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%])

Forum Discussion

Need Help