Total by group at row level

[eyesem]

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!

[parry2k · Accepted solution]

@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!

[parry2k]

@eyesem something I'm missing here, It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.

[parry2k]

@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.⚡

[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!

[eyesem]

Thank 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: