topic Calculate field with different measure and show it as a row in Desktop

Calculate field with different measure and show it as a row

Anonymous — Wed, 09 Mar 2022 16:54:52 GMT

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

Re: Calculate field with different measure and show it as a row

truptis — Wed, 09 Mar 2022 17:12:13 GMT

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.

Re: Calculate field with different measure and show it as a row

JaromBIDEVatDK — Wed, 09 Mar 2022 17:12:37 GMT

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

Re: Calculate field with different measure and show it as a row

JaromBIDEVatDK — Wed, 09 Mar 2022 17:14:06 GMT

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.

Re: Calculate field with different measure and show it as a row

Anonymous — Wed, 09 Mar 2022 17:33:15 GMT

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.

Appreciate your help and looking forward to your reply.

Thanks.

Re: Calculate field with different measure and show it as a row

Anonymous — Wed, 09 Mar 2022 17:36:07 GMT

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!

Re: Calculate field with different measure and show it as a row

truptis — Wed, 09 Mar 2022 17:37:44 GMT

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

Hope this helps you

Re: Calculate field with different measure and show it as a row

Anonymous — Wed, 09 Mar 2022 19:06:49 GMT

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 -

Could you guide me please?

Re: Calculate field with different measure and show it as a row

truptis — Wed, 09 Mar 2022 19:18:25 GMT

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

Re: Calculate field with different measure and show it as a row

Anonymous — Wed, 09 Mar 2022 19:22:39 GMT

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?

Re: Calculate field with different measure and show it as a row

truptis — Wed, 09 Mar 2022 19:42:45 GMT

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/

Re: Calculate field with different measure and show it as a row

Anonymous — Thu, 10 Mar 2022 03:20:39 GMT

Nice