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

[wardaraees]

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

[v-tangjie-msft]

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]

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.

[CoreyP]

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]

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

[CoreyP]

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?

[wardaraees]

which function should i use then to calculate gm%?

[WaqasBashir]

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