Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tomperro
Helper III
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.

 

CategoryTotal 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

 

 

 

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

 

 

 

View solution in original post

10 REPLIES 10
tomperro
Helper III
Helper III

How do I attach pbix file?

lbendlin
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.

This is not working.

I am receiving NaN.

Also, Sumx requires table name as first parameter.

 

see attached.

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

In your file you have this:

tomperro_0-1667217853814.png

 

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.

 

 

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.

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.

 

tomperro_0-1667381516486.png

 

 

 

 

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

 

 

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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