How to make every row display correct result ?

Hi All

I re-post my question , to make it more simple.

My below expression return correct result :-

SALES_ = SUM(SALES[sales])

My below expression return 2 row correct result :-

SALES_SGD = divide([SALES_],'RATE'[_rate])

Above expression is missing SOURCE field. But i don't know where to place it.

Can some one help me make all row display correct result pls.

Now the result i get is wrong with missing row :-

My Expected result :-

Paul Yeo

Need Help

parry2k
4 years ago
admin11 looked bit further, change these measures:

_rate = MAX ('RATE'[RATE] ) SALES_SGD = SUMX( Source, divide([SALES_],[_rate]))

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

smpa01

4 years ago

admin11 us this instead

SALES_SGD_ = 
DIVIDE (
    SUM(SALES[sales]),
    CALCULATE (
        SUM ( 'RATE'[RATE] ),
        TREATAS (
            /*INTERSECT (*/ VALUES ( SALES[SOURCE] )/*, VALUES ( AR[SOURCE] ) )*/,
            'RATE'[SOURCE]
        )
    )
)

12 Replies

parry2k
Super User
4 years ago
admin11 looked bit further, change these measures:

_rate = MAX ('RATE'[RATE] ) SALES_SGD = SUMX( Source, divide([SALES_],[_rate]))

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- admin11
  Memorable Member
  4 years ago
  parry2k
  Thank you , your is correct.
parry2k
Super User
4 years ago
admin11 there is no need to write a complex DAX once you have a good model. Glad it worked out. ,Cheers!!
aj1973
Community Champion
4 years ago
Hi admin11
How are the 2 tables connected?
- admin11
  Memorable Member
  4 years ago
  aj1973
  
  Thank for your help, below is my PBI sample file :-
  
  https://www.dropbox.com/s/uijcikharvvy64t/PBT_V2021_120%20ONLY%202%20ROW.pbix?dl=0
  
  Paul
  - aj1973
    Community Champion
    4 years ago
    admin11
parry2k
Super User
4 years ago
admin11 in your visual use the Source from the Source table not from the sales table and that will fix it

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
smpa01
Community Champion
4 years ago
admin11 you can use this measure instead with the current axis

SALES_SGD = DIVIDE ( [SALES_], CALCULATE ( SUM ( 'RATE'[RATE] ), TREATAS ( INTERSECT ( VALUES ( SALES[SOURCE] ), VALUES ( AR[SOURCE] ) ), 'RATE'[SOURCE] ) ) )
- admin11
  Memorable Member
  4 years ago
  smpa01
  wow Your expression very powerful , few syntax i just come across. now 2 row of SOURCE display data is correct. But I just add few more SOURCE , now still have 5 row data blank.
  
  Below is my PBI file :-
  https://www.dropbox.com/s/dkcmvf8nu8tp49l/PBS_VER0268%20ASK.pbix?dl=0
  
  Paul
  - smpa01
    Community Champion
    4 years ago
    admin11 us this instead
    
    SALES_SGD_ = DIVIDE ( SUM(SALES[sales]), CALCULATE ( SUM ( 'RATE'[RATE] ), TREATAS ( /*INTERSECT (*/ VALUES ( SALES[SOURCE] )/*, VALUES ( AR[SOURCE] ) )*/, 'RATE'[SOURCE] ) ) )

Forum Discussion

How to make every row display correct result ?

12 Replies

Related content

SAMEPERIODLASTYEAR not giving correct results

Correcting Data

Struggling to display correct Total using Measures

Dax correction

Dax Formula correction help