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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Peter_23
Helper IV
Helper IV

Report builder measure issue

Hi community.

I work with report builder, this time is my first report in this tool, I trying to display a table with measures, but I don't make a correct calculation. I mean.

CountryCitystockordersAverage
USNY100300.0
USLA13010

8.0

 

So I have on SM this dimensions: Country, City and measures : stock, orders, average.

the measure "average" is in fact, DIVIDE ( orders , stock ) so the correct values are: NY 30, LA 7,69, eg.

Average is not aggregate in report builder because it is calculated in formule [Average] meanwhile stock and orders are sum. OK

 

If I developmented this table in power bi report the measures are OK.

 

Is there anything is way wrong? Calculation, expression or something.

 

thanks in advance.

 

 

 

 

1 ACCEPTED SOLUTION

Hi, @Peter_23 

As you described, there are many duplications in expressions and measures. Since the total is added after loading the dataset into the report builder and then creating a new visual object, some expressions cannot be avoided.
I think this is because of two different products. At first, it was dedicated to paginated reports and served SQL Server Reporting Service. At that time, expressions were used, which was a long time ago. In recent years, it has developed into Power BI Report Server, which integrates Power BI's semantic model and other functions into it. This is also the current preview stage.
As recommended, complete the writing of measures in the semantic model as much as possible, and then present the data in Power BI Report Builder.
If you have already got the answer to the current problem in the previous reply, you can mark the corresponding reply as a solution so that other people in the community can quickly find help when they encounter similar problems.

 

 

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
v-jianpeng-msft
Community Support
Community Support

Hi, @Peter_23 

Based on the dimensions you showcased, I've made some example data:

vjianpengmsft_0-1736314406424.png

Add this dataset in Report builder. Then insert a tablix:

vjianpengmsft_2-1736314592672.png

vjianpengmsft_3-1736314619131.png

Use the following expression:

 

IIf(Fields!Stock.Value<>0,Fields!Orders.Value/Fields!Stock.Value,0)

 

vjianpengmsft_4-1736314669229.png

Set the display format:

vjianpengmsft_5-1736314692629.png

vjianpengmsft_6-1736314726315.png

Here are the results:

vjianpengmsft_7-1736314758850.png

 

 

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

thanks @v-jianpeng-msft  I have to use expression formule, but is it possible to use measure "averange"?

 

If I don't have group rows defined in the table (Country, city), the measure works, but I would like sumarrized values. 🙂

 

CountryCityStockOrdersAverange
USNY752026.6
USNY251040
USLA10055
USLA30516.6

 

So is there anyway to use measure instead of expression in group row?

 

thanks

Hi, @Peter_23 

If so, I recommend that you connect Power BI Report builder to your semantic model. Create a new summary table in your semantic model with DAX expressions:

 

Table = SUMMARIZE('SalesData','SalesData'[City],'SalesData'[Country],'SalesData'[Orders],SalesData[Stock],"Avuerage",DIVIDE('SalesData'[Orders],'SalesData'[Stock])*100)

 

vjianpengmsft_0-1736402125473.png

Connect to your semantic model in Power BI Report builder:

vjianpengmsft_1-1736402260189.png

vjianpengmsft_2-1736402331297.png

vjianpengmsft_3-1736402420391.png

vjianpengmsft_5-1736402488210.png

vjianpengmsft_4-1736402452788.png

Here, in addition to creating a calculated table in Power BI beforehand, you can also use measures from your semantic model.

vjianpengmsft_6-1736402581430.png

Below is the generated DAX query. If you're good at it, you can write straight into Query Designer instead of going into it.

vjianpengmsft_7-1736402654546.png

vjianpengmsft_8-1736402735818.png

You don't even need to resort to a semantic model. If you have SSAS, you can connect directly to your data source there, then write the corresponding DAX expression, and finally connect it in Report builder.

You can only use expressions in Power BI Report builder, not measures (except in the case of connections to semantic models or SSAS).

If you still decide to use expressions, check out this article below to learn about the syntax of Report builder.

SSRS Avg Function

SQL Server Reporting Services (SSRS) Tutorial

 

 

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-jianpeng-msft thanks

The SM is actually in the service so the live connection is it.

When you said:

 

"You can only use expressions in Power BI Report builder, not measures (except in the case of connections to semantic models or SSAS)."

 

I could be use or could be not? (Measures)

 

 

 


Hi, @Peter_23 

Thank you very much for your reply. If you're connecting to a semantic model in the service in real time, you can use measure.

vjianpengmsft_0-1736736946975.png

vjianpengmsft_1-1736736976742.png

vjianpengmsft_2-1736737045208.png

vjianpengmsft_3-1736737150775.png

You need to write the relevant measure in your semantic model and then connect it.

 

 

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

Thanks @v-jianpeng-msft  Yes, It is. but I trying to add grand total, so the sum(measure) don't have a correct value because it's division function, so which aggregation should be for grand total?

 

thanks in advance.

Hi, @Peter_23 

Based on your description, I created a tablix:

vjianpengmsft_0-1736843097264.png

vjianpengmsft_1-1736843113152.png

This results in the correct summing of the measures:

vjianpengmsft_2-1736843158121.png

 

 

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

@v-jianpeng-msft  thanks for example, the sum are OK. Orders =40 and Stock 230, the measure are incorrect 40 / 230 = 0.173913043 , not 0.376... (totals) is the same issue I'm having too.

 

😥

 

Hi, @Peter_23 

Thank you very much for clarifying this. To do this, I changed my expression as follows:

vjianpengmsft_0-1736906393605.png

vjianpengmsft_1-1736906424373.png

To be able to get the right results:

vjianpengmsft_2-1736906463906.png

 

 

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks again @v-jianpeng-msft  Yes, you can use expression instead of measure defined.

I understand it's not possible to use measure aggregation to total when the measure is division, standard deviation , etc, I should be rewrite the formule in expression. This way I have many measures and expression as same functions as many reports or report builders.

 

I figure out as good practice use defined measure in SM.

 

So I'm going to use expression.

 

 

 

 

Hi, @Peter_23 

As you described, there are many duplications in expressions and measures. Since the total is added after loading the dataset into the report builder and then creating a new visual object, some expressions cannot be avoided.
I think this is because of two different products. At first, it was dedicated to paginated reports and served SQL Server Reporting Service. At that time, expressions were used, which was a long time ago. In recent years, it has developed into Power BI Report Server, which integrates Power BI's semantic model and other functions into it. This is also the current preview stage.
As recommended, complete the writing of measures in the semantic model as much as possible, and then present the data in Power BI Report Builder.
If you have already got the answer to the current problem in the previous reply, you can mark the corresponding reply as a solution so that other people in the community can quickly find help when they encounter similar problems.

 

 

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-jianpeng-msft  I don't know  "This is also the current preview stage", ok I understand.

Thank you so much!

 

 

Helpful resources

Announcements
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!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

Top Kudoed Authors