Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Power Bi measures as rows

Hi All,

 

I have created some measures in PowerBI as per the business requirement

but i can display it as different tables like below as example:

Sales:  
Feb SalesMarch Sales

Percentage diff

 

 

1.5M1.8MSome value
   
Profit:  
Feb ProfitMarch ProfitPercentage diff
1.5 M2.5Msome value
   
Loss:  
Feb LossMarch LossPercentage diff
500k200ksome value

 

 

 

I have created sales table chart from  content table in powerbi with column content_sales, profit table chart from content table with content_profit and same for loss table chart
and date is also considered from same content table 

I will include dax formula which I used for getting those measures

Feb sales = Feb Like = VAR previous_month = EOMONTH(TODAY(),-2)
RETURN
CALCULATE(
DISTINCTCOUNT('Content'[content_sales]),
FILTER(
'Fact Content',
'Content'[date] >= DATE(year(previous_month), month(previous_month), 1)
&& 'Content'[date] <= previous_month
)
)
Like this I achieved Feb profit and feb loss  and same as for march month sales, profit and loss

 

But the business want as below:

FunctionFeb MarchPercentage diff
Sales1.5M1.8Msome value
Profit1.5M2.5Msome value
Loss500k200ksome value

 

How can I achieve this please help me out...

 

Thanks in advance

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    I'm not clear about your requirement, could you please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi,

      Okay.

      I have table in frontend fields  section as below:


      and like this sum of loss and sum of profit also


      In backend the table is like

      I have given an example from excel


      I have written logic for sales for previous month and last before month and average sales percentage for last two months based on date field,
      Same logics for profit and loss also

      I’m able to show prev month sales, before prev month sales and avg sales percentage in one table chart
      prev month profit, before prev month profit and avg profit percentage in one table chart
      and for loss in another table chart
      Like below

       


      The below is the DAX formula I used to get Prev Month and Bef Prev Month Sales
      Prev Month Sales = VAR previous_month = EOMONTH(TODAY(),-1)
      RETURN
      CALCULATE(
      DISTINCTCOUNT('Content'[Sales]),
      FILTER(
      'Content',
      'Content'[date] >= DATE(year(previous_month), month(previous_month), 1)
      && 'Content'[date] <= previous_month
      )
      )


      Bef Prev Month Sales = VAR previous_month = EOMONTH(TODAY(),-2)
      RETURN
      CALCULATE(
      DISTINCTCOUNT('Content'[Sales]),
      FILTER(
      'Content',
      'Content'[date] >= DATE(year(previous_month), month(previous_month), 1)
      && 'Content'[date] <= previous_month
      )
      )


      For Avg % Difference:

      Sales% = VAR Difference = 'Content'[Prev Month Sales] - 'Content'[Bef Prev Month Sales Sales] RETURN DIVIDE(Difference, 'Content'[Bef Prev Month Sales]) * 100

      Like Same I did for Profit and Loss, in DAX

       

      Same for Profit and loss in different table charts


      But I want to show all of them in one table chart only, like below as:

       

       

      I want to show Sales, Profit, Loss in as a different rows in Function column,
      For Prev Month , Bef Prev month and Avg % Difference columns, I have to show respective sales, profit and loss values in respective rows


      Please give solution

      Thanks in advance