cancel
Showing results 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

Helper III

## Table Visualization Totals Help

Having some difficulty figuring out how to get the total (in red) that I need to show. I am getting 3159 not 926.

3159 is the Total This divided by Total That - should be 3,159 divided by 13 = 926.

 Category Total This Total That This Per That(This / That) Category 1 12,418 5 2,484 Category 2 2,154 1 2,154 Category 3 6,207 0 0 Category 4 11,159 2 5,580 Category 5 9,130 5 1,826 Totals 41,068 13 3,159** Should be the total of This Per That column (12,044) divided by total of Total That (13)(12,044 / 13 = 926)

Current Calculations

Total This = CALCULATE(SUM(MyTable[This]),DATESYTD(ENDOFYEAR(OTI_MORV_Data[Month],"12/31")))
Total That = CALCULATE(SUM(MyTable[That]),DATESYTD(ENDOFYEAR(OTI_MORV_Data[Month],"12/31")))
This Per That = CALCULATE(DIVIDE([Total This],[Total That],0),DATESYTD(ENDOFYEAR(MyTable[Month],"12/31")))
1 ACCEPTED SOLUTION
Super User

``````This per That =
IF(
HASONEVALUE('Table'[Category]),
DIVIDE(
SUM('Table'[Total This]),
SUM('Table'[Total That]),
0
),
DIVIDE(
SUMX('Table', DIVIDE('Table'[Total This], 'Table'[Total That], 0)),
SUMX('Table', 'Table'[Total That]),
0
)
)``````

10 REPLIES 10
Helper III

How do I attach pbix file?

Super User
Super User

DIVIDE(SUMX([Total This]/[Total That]),SUMX([Total That]),0)

Note that this will be confusing to your users as you are switching between simple division and squared division.  This pattern (weighted averages) usually involves multiplication, not division.

Helper III

This is not working.

I am receiving NaN.

Also, Sumx requires table name as first parameter.

Super User

see attached.

Helper III

Thank you. I see it is working in your pbix file.

In your file you have this:

but in my file I have a calculator symbol next to those fields, would that make a difference?

I am using sensitive data, so I can not share my file.

Super User

You are using measures.  That may require a rewrite depending on the data model .

``I am using sensitive data, so I can not share my file.``

I can help you if you provide sample data that fully covers your issue.

Helper III

Even looking at your pbix file, I am seeing that the numbers are not showing what I need.

I need a combination of what you have and what I have.

Super User

``````This per That =
IF(
HASONEVALUE('Table'[Category]),
DIVIDE(
SUM('Table'[Total This]),
SUM('Table'[Total That]),
0
),
DIVIDE(
SUMX('Table', DIVIDE('Table'[Total This], 'Table'[Total That], 0)),
SUMX('Table', 'Table'[Total That]),
0
)
)``````

Helper III

I am unable to attach a file - I do not have that option available and my company is blocking web access to google drive, dropbox, etc.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.