Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Multiple Sale Result by Second Table Values

Hi  Experts   Here is the first question which gives me the correct answer for the first part.  link: https://community.powerbi.com/t5/Desktop/Calculate-Sales-by-Channel-by-Region-by-County-by-Mon...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    I am not sure if the below works.

     

    LookupCost = LOOKUPVALUE( 'Append_Direct Ohds_IAM&OES'[Value], 'Append_Direct Ohds_IAM&OES'[Month], Sales_Data_Master[MonthV2], 'Append_Direct Ohds_IAM&OES'[Year], Sales_Data_Master[Years_In_Date], 'Append_Direct Ohds_IAM&OES'[Channel], Sales_Data_Master[Channel], 'Append_Direct Ohds_IAM&OES'[Country], Sales_Data_Master[Sales_Country], 'Append_Direct Ohds_IAM&OES'[Type of Cost], "4.Purch")
Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

Hi, Anonymous 

Please try to use the below for the calculated column. I combined it with the previous one.

Please kindly let me know if it works or not.

 

 

newcolunm =
DIVIDE (
Facts[Sales],
CALCULATE (
SUMX ( Facts, Facts[Sales] ),
ALL ( Facts ),
VALUES ( Facts[Channel] ),
VALUES ( Facts[Region] ),
VALUES ( Facts[Year] ),
VALUES ( Facts[Country] )
)
)
*

LOOKUPVALUE (
Costs[Value],
Costs[Month], Facts[Month],
Costs[Year], Facts[Year],
Costs[Channel], Facts[Channel]
)

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Kim Can we amend the following so Cost Type in Cost Table = "4.Purch" where i could specific the selection criteria

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, Anonymous 

    I cannot find a link.

    Can you share the link again, please?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      link: https://www.dropbox.com/s/wlpgrtig2nuynvl/Test.pbix?dl=0 

       

      In my master file Append_Direct Ohds_IAM&OES - i have month year split out...

      Kindly just post the DAX not a new file, Please.

       

      ref to Sale_Data_Master for the Calculated column Error

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        5 years ago

        Hi, Anonymous 

        Without the source data connected, I cannot use Power Query to split MonthYear column and have Month-Full-Name in order to connect with Sale_Data_Master's Month Column.