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
eyesem
Helper I
Helper I

Sum rows from visual table

Hi

 

I need some help with something that I guess must be very simple but can't figure out.

I have this visual object (table) with columns from different tables with quite a complex structure between them. I'd like to sum several of the columns from this table in a new column:

caption.png

 

 

 

 

 

 

 

 

relationshipsrelationships

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I tried with this calculations but both seem to be wrong:

 

F_Calculada = sumx('Consumo Click';'Consumo Click'[PFAnexo__c]) - sumx('Cotización de periodo';'Cotización de periodo'[GNF_CVCliente__c]) + sumx('Consumo Click';'Consumo Click'[GNF_descuentoClickAbs__c])
 
F_Calculada = sum('Consumo Click'[PFAnexo__c]) - sum('Cotización de periodo'[GNF_CVCliente__c]) + sum('Consumo Click'[GNF_descuentoClickAbs__c])
 
Any help?
 
Thanks!
4 REPLIES 4
Greg_Deckler
Super User
Super User

Not exactly a measure totals problem but the same technique should work. You would need to essentially replicate your table visualization in a table variable in DAX.  This Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

My problem is when trying to create a measure at the row level. If I sumx a variable from another table I get an out of memory error.

In the table 'consumo click' this works fine:

F_Calculada = sumx('Consumo Click';'Consumo Click'[PFAnexo__c])

But this doesn't:

F_Calculada = sumx('Consumo Click';'Consumo Click'[PFAnexo__c]) - sumx('Cotización de periodo';'Cotización de periodo'[GNF_CVClientePrima_Click__c])

 "The report ran out of memory, and the application must close"

 

I just need to add 3 values I'm already seeing in my visual table for each row!

Anonymous
Not applicable

It's a good practice if you avoid the measures that are designed keeping specific visualization in mind.

Let us consider an example based on the data provided above.

1. The years are identifiers for the rows in the table

2. If any other column or row is added like month then the behavior of the measure will be different

The part of your formula for the calculation the total measure would work well on the detail rows when iterating over VALUES(Table[Year] instead of Table itself:

MyMeasure3 = SUMX(FILTER(VALUES(Table[Year]),[Amount]>1000),[Amount]-1000)
However, VALUES(Table[Year]) would contain only one row while the filtered table is empty when [Amount]<=1000.

This means that rows for years with [Amount] lower than 1000 should have a blank value. In case you want to have 0 instead of blank, you can just add 0 to the result:

MyMeasure3 = SUMX(FILTER(VALUES(Table[Year]),[Amount]>1000),[Amount]-1000) + 0
Hope this helps!!

Admin

Apps4rent | o365cloudexperts | clouddesktoponline

amitchandak
Super User
Super User

I think the formula is correct. I doubt so the relation is not working. The other thing is that you can group is a level using summarize. example

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

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.