Sum Unique Quantity for Total Correct

[GKs]

I have worked with this for a couple of days and I really can't find the solution. I appreciate the support you can give me.
In a data that has the color, id number and quantity I am trying to unify a single row.
Primary (Yellow, Black, Soft, Red) and Secondary (Gray and blue), so I want to group the secondary into the primary.

I have used all these combinations of formulas but none gives me the expected result. the ones that are closer to expected are these:

SUMX(DISTINCT(Data[Id]),FIRSTNONBLANK(Data[Second],CALCULATE(SUM(Data[Qty],FILTER(Data,Data[Id] = EARLIER(Data[Id])))))

SUMX(DISTINCT(Data[Id]),LASTNONBLANK(Data[Third],SUM(Data[Third])))

This are other combination

SUMX(GROUPBY(Data,Data[Color],Data[Qty]),SUM(Data[Qty]))

SUMX( SUMMARIZE( VALUES(Data[Id]), "Total Second",SUM(Data[Third]) ), MIN(Data[Second]))

I enclose an image of how I would expect it to show the results. I took this data to Excel and made it manually for the purpose of the example.

[lc_finance]

Hi @GKs ,

You can find my proposed solution here.

I created calculated columns for First and Second:

First = IF([Category] IN {"Yellow", "Black", "Red", "White"},[Qty],0)
Second = IF(NOT [Category] IN {"Yellow", "Black", "Red", "White"},[Qty],0)

And a calculated measure for Third:

Third = 

var categoryIsTotal = NOT ISFILTERED(Colors[Category])
var categoryIsPrimary = SELECTEDVALUE(Colors[Category]) IN {"Yellow", "Black", "Red", "White"}

RETURN SWITCH(TRUE,
 categoryIsPrimary, CALCULATE(SUM('Colors'[Second]),ALLEXCEPT('Colors','Colors'[Id]))
 ,categoryIsTotal, SUMX(VALUES('Colors'[Id]), CALCULATE(SUM('Colors'[Second])) )
)

Finally, here is a screenshot:

Let me know if this is what you are looking for.

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

[GKs]

Hi @lc_finance 

I appreciate your help the first and second columns work great.
For the third measure, I replicated it, however, it is totaling based on all the IDs in the case that I want to see it as a summary of all the records by categories.

The following image is how it is being displayed. Try to extract an part for the previous example, but taking it to the full data you can see the amount of 637,628.
Expected Column: Third, Blue and Gray should be 0, and should show only the quantities, where the ID has First.
Looking for the quantity that were sold with colors First.

[lc_finance]

Hi @GKs ,

can you share your Power BI file?

That will help me to understand why you get those results and how to fix the formula.

You can use One Drive, Google Drive or another similar tool to share your Power BI file.

Regards,

LC

[GKs]

https://drive.google.com/file/d/1fIO6UPk-P68ZG-353EjsU2EeanQOByVP/view?usp=sharing

[lc_finance]

Hi @GKs ,

I just checked and your Google Drive file is private. 

Let me know when you make it public so I can access it,

LC

[GKs]

Please try again, thanks you for your help

[lc_finance]

Hi @GKs ,

I apologize for the late reply.

I tried to modify the formula on the old example, you can download it from here.

It's quite a complicated formula, so I used the old example as it has fewer rows so it's easier for testing and understanding the formula.

Can you let me know if that is what you are looking for?

LC

[GKs]

id used as filter the formula does what is expected but if you take the id from the box or filter the value is equal to the second.
In essence, what is expected is to see a total real value without the need to filter id by id.
The formula is the ideal I keep using it to see if I can perform the calculation as expected.
I think the topic would be SUMX since it is adding a whole or maybe doing a SUM with FILTER.

[lc_finance]

Hi @GKs 

Based on the Power BI I sent yesterday, Third is no longer equal to Second even if Id is taken away from the table.

In the example, the total for Third is 84 and the total for Second is 241.

84 is the sum of 68 for transaction 8326 and 16 for 8468.

A paste a screenshot below.

Is this what you are looking for?

LC

[GKs]

Yes, you did it.
But you won't believe me for some reason, my Power BI doesn't SUM well, download the file I did't make any modification just remove the id from the table. I don't know why this happens, If it's an update that I don't have or maybe I did.

[lc_finance · Accepted solution]

Hi @GKs 

Maybe there was a problem with the previous link. Can you try this one?

Let me know if it works

LC