Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Calculate field with different measure and show it as a row

Hi All, 

 

I have barely created a table chart in PowerBI and am still learning. I have received one reporting project and am not sure how to achieve the results. Please see the sample screenshot below. So I have a date field, multiple measures and multiple fields.

  • E.g. I have customer id field and I have another field called active/archive customer. So i want to calculate no of active customer. What action should I take? Add a new column? 
  • Same for Cloud Qty - I want to calculate Clound qty based on Quntity measure. I have another column which shows cloud, subscription and so on. 
  • My major questions are - 
    • How do I caluclate these field as it is based on all different measures?
    • And how do I put them in a row as shown in the below table?

Screen Shot 2022-03-09 at 11.45.46 AM.png

I would really appreciate it if anyone can guide me to achieve this? This is a very urgent case.

 

Thanks

1 ACCEPTED SOLUTION

Hi @Anonymous 
Please Try this formula:
Sales_Result = Calculate(Sum(AnnualValue), filter(Tablename, stage_name="Closed" || stage_name= "Hold"))

Hope this helps you

View solution in original post

11 REPLIES 11
truptis
Community Champion
Community Champion

@Anonymous Glad to know. 
Request you to mark this as a solution. 
For DAX you can search functions on microsoft docs https://docs.microsoft.com/en-us/dax/ or else refer this :
https://dax.guide/ 

JaromBIDEVatDK
Helper II
Helper II

I would suggest the following:

1) create a Matrix visual

2) Write your measures using the measure not calculated column

Measure 1 = Count(Column in table) 

Measure 2 = sum(column in table)...

3) Then you can place column that describes the data in the axis field

4) drop the date range in the column header to categorize the measures

If each row is a different calculation then each row becomes its own measure. In that case might flip the matrix so the rows in your picture becomes the categories or the column header and the date becomes the axis. 

Anonymous
Not applicable

@JaromBIDEVatDK  - Thanks a lot for your quick reply. The screenshot is hypothetical example but I have similar kind of requirements. 

 

  • The problem is, my new measure is just not the count or sum. 
  • E.g. Let's say I want to see the sum of sales results but also want to put the filter on stage = 'Closed' 
    • Sales results = sum of (Annual value) but I also want to apply filter on this where stage(field) = 'Closed' or 'Hold'
    • And same multiple filter for other measures 

So this is what I followed but I think my formula is wrong as NULL shouldn't be there. 

 

Sales Result = if (OR([STAGE_NAME]="Closed",[STAGE_NAME]="Hold"), "Sales Results","NULL")

 

Below are the results, how it looks in my report -

Screen Shot 2022-03-09 at 12.20.30 PM.png

sgajjar_0-1646846508199.png

 

And if I add another field, it just goes to Values and not rows.  Not sure why. 

(Sorry have to hide the number due to integrity of data)

 

Could you explain a bit more on flip the metrix part?

 

I have been using Tableau so I understand the fields and measure and logics but PowerBI seems little difficult to me now. 

 

 

Appreciate your help and looking forward to your reply. 

 

Thanks. 

Hi @Anonymous 
Please Try this formula:
Sales_Result = Calculate(Sum(AnnualValue), filter(Tablename, stage_name="Closed" || stage_name= "Hold"))

Hope this helps you

Anonymous
Not applicable

Hi @truptis  - 

I used your formula and created a new measure as Sales_Results_new. But it isn't allowing me to put it in rows. So I had to place it in Values and it isn't showing headers in the report like Sales_results_new. when I create another measure 'Sales_results_new1', and put it in values, it shows names in columns. See the screenshot below -

 

 

Screen Shot 2022-03-09 at 2.03.04 PM.pngScreen Shot 2022-03-09 at 2.03.30 PM.png

 

Could you guide me please? 

Hi @Anonymous ,
select the visual, and go to the format option-> Type "rows" -> there will be a toggle called "Show on Rows" turn that ON 
by this way, you will see the values on Rows

truptis_0-1646853318126.png

Regards,
TruptiS

Anonymous
Not applicable

Nice

Anonymous
Not applicable

@truptis  - it worked! Thanks a lot. It is really helpful.

Quick question  - is there any online document where I can see the PowerBI related formulas? 

truptis
Community Champion
Community Champion

Hi @Anonymous 

Could you please share a sample data that you have and the sample output that you need? Because your requirement isn't much clear to me but it seems easy.

Anonymous
Not applicable

@truptis  - 

I'll try to create sample data if possible and share the details with you. In the meantime, please check my comment to JaromBIDEVatDK above. You might get better idea. 

 

I really appreciate your quick reply on this. 

 

Thank you! 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.