Forum Discussion
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.
| Country | City | stock | orders | Average |
| US | NY | 100 | 30 | 0.0 |
| US | LA | 130 | 10 | 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.
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.
12 Replies
Hi, Peter_23
Based on the dimensions you showcased, I've made some example data:
Add this dataset in Report builder. Then insert a tablix:
Use the following expression:
IIf(Fields!Stock.Value<>0,Fields!Orders.Value/Fields!Stock.Value,0)Set the display format:
Here are the results:
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 Anonymous 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. 🙂
Country City Stock Orders Averange US NY 75 20 26.6 US NY 25 10 40 US LA 100 5 5 US LA 30 5 16.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)Connect to your semantic model in Power BI Report builder:
Here, in addition to creating a calculated table in Power BI beforehand, you can also use measures from your semantic model.
Below is the generated DAX query. If you're good at it, you can write straight into Query Designer instead of going into it.
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.
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.
