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 used the below DAX to for "total Buom_act_distreceipts_2 and Actual_vendor_percentage"

Total BUom_Act_DistReceipts 2 =
CALCULATE (
    SUM ( bw_csp_act[BUoM_Act_DistrReceipts] ),
    ALLSELECTED ( bw_csp_act[vendor] )
)
Actual_Vendor_Percentage =
DIVIDE (
    SUM ( bw_csp_act[BUoM_Act_DistrReceipts] ),
    [Total BUom_Act_DistReceipts 2],
    1)

I would like for the actual vendor percentage for the materials in blue to be a portion of their total value(just as i have for the material highlighted in yellow)
It's proven to be difficult since i can't find a common denominator, any suggestion would be really appreciated and i would also like to know if it's an impossible task

9 Replies

  • olgad's avatar
    olgad
    Resident Rockstar
    2 years ago

    Hi, 
    provided you have the same material number  or description, because right now your material description is technically different because it contains vendor names, you can use the following:

    Total BUom_Act_DistReceipts 2 =
    var NumofVendors=CALCULATE (Count( T1[Vendor] ),ALLSELECTED(  T1[Vendor] ))
     var NumofMaterial=CALCULATE (Count( T1[GCAS] ),ALLEXCEPT(T1, T1[Mat Descr] ) )
     RETURN
    if(NumofVendors>1,
    CALCULATE (SUM ( T1[Dist receipts] ),ALLSELECTED ( T1[Vendor] )),
    if(NumofMaterial>1,
    CALCULATE (SUM ( T1[Dist receipts] ),ALLExcept ( T1,T1[Mat Descr] )),
    Sum(T1[Dist receipts])))

     



    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you Olgad, appreciate your response. You are totally right as these materials appear to be different in all attribute(code, description and vendor). However it is a peculiar situation i have been tasked with and my understanding is that i have to find something unique to these materials or create something unique myself. Your query however works where i have same description, thanks. If you think of something else that can help in this situation, i'd appreciate the thought.

  • olgad's avatar
    olgad
    Resident Rockstar
    2 years ago

    Check this out, you have the name of the vendor in your Mat Description,  you create a duplicate of your material and split it into the first part until vendor and last part with vendor, then you split the vendor column into one part of the vendor itself, then you compare them, if it is the same, then you take the base Mat description, without vendor name, if they are not the same then you take material as it was. 

    Might be a bit confusing, but check the power query and if you still have questions, let me know. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Olgad, This is amazing and i think it will solve the problem. I have had a look at the Pbix file and it looks great. Kinda confused on the power query part, could you help me with the power query you made use of in this example? Otherwise i can try to figure it out and let you know if it works for me. Thanks a bunch!

    • 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

         

  • olgad's avatar
    olgad
    Resident Rockstar
    2 years ago

    1. Duplicated the column

    2.  Splitted that Material Copy column and Transformed to be upper text to mach your Vendor Upper Text- 

    3. Duplicated the vendor, splitted it too

    4. Finally said if there is a match, then use the new material column, if no, the original. 

    Now you can use this Mat column for calculation.