cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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?

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

Thanks

1 ACCEPTED SOLUTION
Community Champion

Hi @Anonymous
Sales_Result = Calculate(Sum(AnnualValue), filter(Tablename, stage_name="Closed" || stage_name= "Hold"))

Hope this helps you

11 REPLIES 11
Community Champion

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/

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

Helper II

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 -

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.

Thanks.

Community Champion

Hi @Anonymous
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 -

Community Champion

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

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?

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

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.

Thank you!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors