Tricky Switch or If Formula

[Anonymous]

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 

[Jihwan_Kim · Accepted solution]

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

[Fowmy]

@Anonymous 

Follow steps in the attachement.

[Anonymous]

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

[Fowmy]

@Anonymous 

I included the other fields as well, please check the attachemnt.

[amitchandak]

@Anonymous , to me this seems like a measure in a row using " Show on row"

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

[Anonymous]

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