Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Help

  Hi in the above, i have in yellow a material with the same code coming from two suppliers and in blue i have a amaterial which have different code and is coming from different buyers. I have...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Thanks Olgad, I have applied the power query and achieved a common Material description for both materials as highlighted below. However the Total receipts are still summed up individually

 

olgad's avatar
olgad
Resident Rockstar
2 years ago

the only thing that comes to my mind, go back to power query , select Material description and trim it, may be a space is left somewhere and pbi still treats it as two separate materials.

 

because we have identical measure and for me it works

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you Olgad, been very helpful! I trimed and for some reason i seem to get the right total receipt now. However it seem to be static and not affected when you play around with the date as seen in the second screenshot

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous ,

       

      Thank you olgad  for the quick response and solution. Here are some of my additions:

       

      Please try this measure:

      Total Receipts = 
var NumofVendors=CALCULATE(COUNT(bw_csp_act[vendor]),ALLSELECTED(bw_csp_act[vendor]))
var NumofMaterial=CALCULATE(COUNT(bw_csp_act[MatNr]),ALLEXCEPT(bw_csp_act,'bw_csp_act'[Material-Description]))
RETURN
IF(NumofVendors>1,
CALCULATE(SUM('bw_csp_act'[BUoM_Act_DistrReceipts]),ALLSELECTED('bw_csp_act'[vendor])),
IF(NumofMaterial>1,CALCULATE (
    SUM ( bw_csp_act[BUoM_Act_DistrReceipts] ),
    FILTER (
        ALLSELECTED ( bw_csp_act ),
        bw_csp_act[Material-Description]
            = SELECTEDVALUE ( bw_csp_act[Material-Description] )
    )),SUM(bw_csp_act[BUoM_Act_DistrReceipts])))

      If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

       

      Best Regards,

      Neeko Tang

      If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.