Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Tricky Switch or If Formula

Hi Experts

 

See attached sample PBIX file i am trying to Produce a  P&L Statement and i Cannot transpose the data as my final table has 150 columns in it. 

 

What i am trying to Produce is the folllowing table in Power BI - which is the sate of my P&L...

  USD 
 Gross Sales    25,155.00
 Rebate          640.00
 Net Sale    24,515.00

 

Sample File

https://www.dropbox.com/s/8b6zd95vxn7mmr9/Net%20Sales.pbix?dl=0 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    Please correct me if I wrongly understood your question.

    I am not sure why the two tables are connected, but I did not change any relationship, and I just left it as it is.

    Please check the below picture and the measure, whether it is what you are looking for.

     

     

    USD =
    SWITCH (
    SELECTEDVALUE ( DimHierarchy[Index] ),
    1, SUM ( FACTSales[Gross Sales (USD)] ),
    2, CALCULATE ( SUM ( FACTSales[Rebate (USD)] ), REMOVEFILTERS ( DimHierarchy ) ),
    3, CALCULATE ( SUM ( FACTSales[Net Sales (USD)] ), REMOVEFILTERS ( DimHierarchy ) )
    )

     

     

    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, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, Anonymous 

    Please correct me if I wrongly understood your question.

    I am not sure why the two tables are connected, but I did not change any relationship, and I just left it as it is.

    Please check the below picture and the measure, whether it is what you are looking for.

     

     

    USD =
    SWITCH (
    SELECTEDVALUE ( DimHierarchy[Index] ),
    1, SUM ( FACTSales[Gross Sales (USD)] ),
    2, CALCULATE ( SUM ( FACTSales[Rebate (USD)] ), REMOVEFILTERS ( DimHierarchy ) ),
    3, CALCULATE ( SUM ( FACTSales[Net Sales (USD)] ), REMOVEFILTERS ( DimHierarchy ) )
    )

     

     

    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, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Amit - that is not going to work in the long run when doing income statements...

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Excellent idea but i need to keep region channel and year and month columns,....