Forum Discussion
Helper ITotal by group at row level
Hi
I need to get a measure 'Qcartera' as the sum of all the QA values for each distinct NISS in the filtered view of the table (in this example the applied filters are narrowing down the table to 2 distinct NISS).
I've tried this these options, but I always get as Qcartera the QA of each NISS, I need the sum of all the different NISS (the expected value is 36.301.384 in each row):
Qcartera = SUMX(SUMMARIZE('Conceptos Contratados Datos','Conceptos Contratados Dimensiones'[NISS],"Qcartera",SUM('Conceptos Contratados Datos'[QA])),[Qcartera])
Qcartera = CALCULATE(SUM('Conceptos Contratados Datos'[QA]),SUMMARIZE('Conceptos Contratados Datos','Conceptos Contratados Dimensiones'[NISS],"QaTotal",SUM('Conceptos Contratados Datos'[QA])))
Qcartera = SUMX(VALUES('Conceptos Contratados Dimensiones'[NISS]),CALCULATE(MAX('Conceptos Contratados Datos'[QA])))
Note: 'Conceptos Contratado Datos' and 'Conceptos Contratados Dimensiones' are related by an unique id row by row.
Any help?
Thanks!
eyesem try this measure, now I understood your model better:
Measure = CALCULATE( SUMX ( VALUES ( 'Dimension'[CODE] ), CALCULATE (MAX ( 'Fact'[QA] ) ) ), FILTER ( ALLSELECTED ('Fact' ), COUNT ('Fact'[INDEX] ) >=1 ) )Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
6 Replies
- parry2k
Super User
eyesem try this measure
Measure = CALCULATE ( SUMX ( ALL ( Data[QA], Data[NISS] ) , CALCULATE ( MAX ( Data[QA] ) ) ), ALL () )Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
eyesemThank you for your reply.
This doesn't seem to be working either. It is creating duplicated rows and the measure seems to be adding all the rows, not only the two in the table:
- eyesem
I've prepared a simplified data set for this purpose:
https://drive.google.com/drive/folders/1-bACZciPecBj7LD4wGKrOlhoiyCQGY4M?usp=sharing
When the view is filtered by April'21 we'll have only 4 different codes, and the expected result of the measure is the sum of QA of these 4 codes (273.681.384)
What I'm really trying to do is to weight the Value by the Qtotal.
Thank you!
- parry2k
eyesem try this measure, now I understood your model better:
Measure = CALCULATE( SUMX ( VALUES ( 'Dimension'[CODE] ), CALCULATE (MAX ( 'Fact'[QA] ) ) ), FILTER ( ALLSELECTED ('Fact' ), COUNT ('Fact'[INDEX] ) >=1 ) )Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- eyesem
Wow, thank you very much. I would have never been able to do it!