Forum Discussion

GKs's avatar
GKs
Frequent Visitor
6 years ago
Solved

Sum Unique Quantity for Total Correct

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.
 
Need Help

11 Replies

  • lc_finance's avatar
    lc_finance
    Solution Sage
    6 years ago

    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's avatar
      GKs
      Frequent Visitor
      6 years ago

      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's avatar
        lc_finance
        Solution Sage
        6 years ago

        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