Table Visualization Totals Help

[tomperro]

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")))

[tomperro]

How do I attach pbix file?

[lbendlin]

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

[lbendlin]

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.

[tomperro]

This is not working.

I am receiving NaN.

Also, Sumx requires table name as first parameter.

I 

[lbendlin]

see attached.

[tomperro]

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.

[lbendlin]

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.

[tomperro]

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.

[lbendlin · Accepted solution]

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 
      ) 
    )

[tomperro]

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.