Skip to main content
cancel
Showing results for 
Search instead 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

Reply
wardaraees
Frequent Visitor

power bi matrix visual does not show the correct total value for % measures calculation

I have my data coming form an SQL table consisting of 3 columns

sales, grossmargin, gm % = (grossmargin/sales),

 

issue: power bi matrix visual does not show the correct total and subtotal value for gm % column.

 

when i am creating a measure in power bi using divide function as shown below:

GM%-MEASURE = DIVIDE(sum(s2[grossmargin]),sum(s2[sales]),0)

it is giving out correct total and subtotal value for gm %.

 

but the column that is calculated on SQL level gm % = (grossmargin/sales), is not giving out correct total value in the visual

 

I want to perform all the calculations on SQL level in order to speed up my report loading time, i have just used a small data example for explaining my issue, in real time i have a big dataset and it takes so much time to load the % measures ( if i calculate them on the front end - BI Level using DAX)

 

How could i fix this problem? Any alternate way to calculate these measures at the SQL level and get correct results for total values in the visual??

@PowerBI @Powerviz @powerbi3 @PowerBig @npowerbi 

below is the attached screenshot of the matrix visual

 

 

wardaraees_1-1700067068612.png

 

 

7 REPLIES 7
v-tangjie-msft
Community Support
Community Support

Hi @wardaraees ,

 

As @CoreyP  said, you need to create new tables in sql to aggregate by month. When you calculate the gm % column in the database and import it into PBI Desktop, by design, if you don't create additional measure, you will only be able to select sum, average, etc. for that column, and the totals and sub-totals will show sum, average, etc. as well.
So the best option is to create a measure.

GM%-MEASURE = DIVIDE(sum(s2[grossmargin]),sum(s2[sales]),0)


You can optimize the performance of the report by filtering the data to optimize the model, etc., see

Optimization guide for Power BI - Power BI | Microsoft Learn

How to Optimize Power BI Reports: Power BI Best Practices | by MAQ Software | MAQ Software | Medium

 

Best Regards,

Neeko Tang

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

wardaraees
Frequent Visitor

wardaraees_0-1700110181131.png

 




this is my raw data, total of gm % column is incorrect in power bi visual.
SQL-level calculation are giving out correct results for row by row level, but the total value is not getting correctly calculated in the visual.

Oh, yeah, exactly. In SQL, your granularity is at the day level. So, you either have to do some kind of aggregate measure in Power BI if you want GM% at a different granularity, like Month. Or you need to create a new SQL table pre-aggregating by month; but in doing so, you will lose your daily granularity. 

 

I don't understand why you don't want to use measures in Power BI for aggregations? Power BI is literally designed for this, and simple calculations like SUM and DIVIDE should not dramatically decrease your report's performance or load time, even if you have a fairly large dataset. 

wardaraees
Frequent Visitor

I am doing sum of both columns individually i.e. sum(sales) and sum(gross margin), and then dividing it(in SQL), but after getting this data into BI, the gm % is not showing correct total value in the visual

Right, because you don't SUM percentages to get a total percentage. If you're doing all your aggregations in SQL, and not Power BI, and you just want to display them as they exist in SQL within a Power BI visual, don't use an aggregate function. What's your raw data look like?

which function should i use then to calculate gm%?

WaqasBashir
New Member

Do sum in indiviual comluns then divide and on row totals and grand total dont choose average

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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