Sum and totals not working

[BarryB75]

I'm having trouble with formulas and the way the results sum in Power BI Desktop.

For Profit Margin, I take Costs Column Divided by Revenue Column which equals the decimal number that I convert to percentage.  It works fine for single orders, but when I combine orders the total doesn't total up all of the Costs column and Divide by the Total of the Revenue column.  Instead, it takes all of the results and sums, averages, or whatever I choose as the method and calculates it that way.

Any ideas on what I'm doing wrong or if there is a special function or command that I'm leaving out?

[Greg_Deckler]

Can you post some sample data and a screen shot of what you are seeing and what is wrong?

[BarryB75]

I appreciate the offer for help.  I'll see if I can get a screen shot.  Digging into it more, one of the columns I was using was imported in from an SSAS model as a calculated measure.  Those for some reason, always come over as text.  I changed the formatting and everything, but when I use this as part of my equation the totals don't work.  When I re-create the calculated field with the base date from the import, and use the new calculated column to as part of my new column, the totals seem to be working.  

It's really odd.

[v-qiuyu-msft]

Hi @BarryB75,

Based on my test, if get data from SSAS cube, the imported numeric column data type won't be changed to text, and it can be also used for calculating totals in a measure.

In your scenario, would you please share detail steps for us to reproduce the issue?

Best Regards,
Qiuyun Yu

[BarryB75]

Here is an example.  Margin percentage is a calculated measure.  On live connections, it works fine, but when I import it, you can see that there is no sigma sign beside it.  In the table and the modeling, it comes over as text.  This is a calculated measure that uses two other measures (charges and costs) to calculate.  

Imports as text

[v-qiuyu-msft]

Hi @BarryB75,

I can reproduce the issue like yours. If I create a new calculated measure('test') by divide one measure('Internet Sales Amount') and another measure('Reseller Sales Amount') on SSAS side, the measure test will be treated as Text data type in import mode. But in live connection, the measure test is fine.

For this issue, I will report it internally and keep you updated once I get any feedback.

Best Regards,
Qiuyun Yu

[BarryB75]

Below is an example of what I was talking about in my first post.  The margin percentage should be margin divided by charges.  It is correct on individual lines, but the totals add the results (margin percentage) instead of taking the total of the margin column and the total of the charges column.  I made a custom column and selected the margin column and divided by the charges column.  Same result.  The individual lines were fine, the totals did the same thing.  Also, the margin column came over as text (like the earlier example on the margin percentage) so I had to re-model it.

[v-qiuyu-msft]

Hi @BarryB75,

To make the total row of Margin Percentage return the value by dividing the total of the margin column and the total of the charges, you can create a measure like below:

Measure 2 = CALCULATE(DIVIDE(SUM('Adventure Works (2)'[Internet Sales Amount]),SUM('Adventure Works (2)'[Reseller Sales Amount])))

Best Regards,
Qiuyun Yu

[Greg_Deckler]

Yeah, that sounds very strange!