Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

sum total only once

  • 1.  sum total only once

    Posted 10 days ago

    Hi,

    I would like to make a measure that could calculate only once when the client has the same name, ID, Age, Value, category and different programs name like below:

    ame ID Age Value Category Programs Name Date
    Ruth CE440 25 100.00 Legal        Case management 10/31/2019
    Ruth CE440 25 100.00 Legal Universal Access 10/31/2019
    Judy DA001 35 50.00 English Case management 07/02/2019

    I would like to create a measure which can sum value total = 150.00, not 250.00. Because there are different programs listed under Programs Name column for the same person and I would like to sum the value for "Ruth" only once and it does not matter which programs name listed, it could be either program name. 

    In power bi when I made a measure: total Value = SUMX(sheet1,sheet1[Value]) which showed total: 250 not 150.00.

    And I made another measure: =CALCULATE([total Value],DISTINCT(sheet1[Programs Name])), it still showed me 250, not 150. 

    Could someone please help and much appreciated your help!

    Best,

    Beckmann



    ------------------------------
    beckmann Jim
    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: sum total only once

    Top Contributor
    Posted 10 days ago
      |   view attached
    Hi @beckmann Jim,

    Please can you try the following code:

    Total = CALCULATE(
    SUM('Table'[Value]), ALL('Table'[Programs Name])
    )
    This will remove the filters from the Program Name column.

    Hope this helps.

    Thank you,


    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Attachment(s)

    pbix
    Jim Beckmann.pbix   48K 1 version
    Conference-PBI_200x200


  • 3.  RE: sum total only once

    Posted 9 days ago
    Hi Vishesh Jain,

    Thank you very much for your sample and your explanation. For some reason, I implemented your measure for the card which still shows the wrong aggregated dollar amount 1855 and it should only sum once for Ruth Basilio and the correct amount should be 1360. Not sure why the measure still does not work as follow:

    Did I make something wrong?
    Your helpful answer would be very much appreciated!
    Beckmann


    ------------------------------
    beckmann Jim
    ------------------------------

    Conference-PBI_200x200


  • 4.  RE: sum total only once

    Posted 8 days ago
      |   view attached
    Hi Vishesh Jain,

    Thanks so much for your time for the further investigation and I am enclosing the sample file for your reference.

    This was what I done in the steps:
    1. Loaded excel xlsx file into Query Editor.
    2. Select column E to AU, then select the Transform tab.
    3. Under Unpivot Columns, I have chosen the Unpivot only selected Columns and named the attribute column to "Indicator Category" and the Value column to be "Dollar Amount".
    4. On the canvas dragged Full Name, Unique ID, Age, Indicator Category, Programs Name, Dollar Amount, Assessment Date into table.
    5. Drag the slicer to the canvas and drag the "agency name" into it.
    6. Drag another slicer to the canvas and drag the "Indicator Category" into it.
    7. Drag a card and put your measure there.
    8. Then Select the P.F.Breasee Foundation - Wilshire FamilySource... from Agency Name slicer, then you can see the summed dollar amount shows the total for FS Intensive Case Management and FS Universal Access for the ID CE440D0D4 and name is Ruth Basilio. And She should be summed $495.00 only for FS Intensive Case Management, should not adding the $495.00 for FS Universal Access.
    9. Now the challenge is that I drag the card which I want to sum the Dollar Amount once if the client has the same Unique ID, but have two different "Programs Name" ( FS Universal Access, FS Intensive Case Management).
    10. If Unique Client only has dollar amount for the Programs Name "FS Universal Access", it is fine.
    11. If Unique Client only has dollar amount for the Programs Name " FS Intensive Case Management", it is fine.
    12. But if Unique Client has dollar amount for the Programs Name "FS Universal Access" and has dollar amount for the Programs Name " FS Intensive Case Management", then I have to ignore the dollar amount for the Programs Name "FS Universal Access". 
    13. If Unique Client has Null value, it is fine. I only wanted to capture the dollar amount to be summed for the Programs Name " FS Intensive Case Management" if Unique Client has both dollar amount for the Programs Name "FS Universal Access" and the Programs Name " FS Intensive Case Management"
    Hopefully my explanation is not making you even more confused.
    Thank you!
    Danke Sehr!

    ------------------------------
    beckmann Jim
    ------------------------------

    Attachment(s)

    xlsx
    sample.xlsx   106K 1 version
    Conference-PBI_200x200


  • 5.  RE: sum total only once

    Posted 9 days ago

    I think the answer you are looking for is outlined in this article https://stackoverflow.com/questions/42962096/how-to-sum-distinct-values-in-a-column-based-on-a-unique-date-in-another-column

    The equation you are looking for is:

    Sum By ID =
    SUMX(GROUPBY(sheet1,Sheet1[ID], "Current ID", MAXX(CURRENTGROUP(), [value])), [Current ID])


    ------------------------------
    Trey Holst
    Global Network Admin
    Denver
    ------------------------------

    Conference-PBI_200x200


  • 6.  RE: sum total only once

    Top Contributor
    Posted 9 days ago
    Hi @beckmann Jim,

    Please can you upload your sample file as I am getting the correct result.

    However I know that data you provided is limited, so will have to check on a broader sample.

    Here is a screenshot of my result:


    Thank you,


    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Conference-PBI_200x200


  • 7.  RE: sum total only once

    Top Contributor
    Posted 8 days ago
    Edited by Vishesh Jain 8 days ago
      |   view attached
    Hi @beckmann Jim,

    You response cleared up your requirement and please correct me if I am wrong here.

    If an ID/Name has 2 program names, i.e. if they have both 'Intensive Case Management' and 'Universal Access', then you want to ignore one of them when totaling​.
    I'll try to come up with something, but as of now I have combed through your entire data and I haven't found a single record that has this condition. Even for Ruth Basilio, there was only one program name.

    The rest of the response is all data modeling related:
    Please can you replace your nulls with 0s or remove them altogether as it will reduce your file size considerably and improve performance.
    I am attaching my PBIX here, so kindly check the steps of the data modeling. I tried to replicate whatever you wrote. It will be best if you could share your file or validate mine.

    Meanwhile, I'll try to work on the measure and get back to you.

    Thanks,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Attachment(s)

    pbix
    Sum Total Once.pbix   116K 1 version
    Conference-PBI_200x200


  • 8.  RE: sum total only once

    Top Contributor
    Posted 8 days ago
    Edited by Vishesh Jain 8 days ago
    *******IGNORE THIS MESSAGE AND THE FILES*******

    I have edited the excel file to add a record for Ruth Basilio and change the program name to 'Universal Access' to have multiple record, in order to test the code.

    I am attaching both the excel and the PBIX.

    You may disregard the previous PBIX.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Attachment(s)

    xlsx
    sample.xlsx   106K 1 version
    pbix
    Sum Total Once.pbix   121K 1 version
    Conference-PBI_200x200


  • 9.  RE: sum total only once

    Posted 7 days ago
      |   view attached
    Hi Vishesh Jain,

    Yes, your understood was correct!
    I am enclosing the original raw excel CSV format file. I will clear up the NULL to be 0s or blank later in my power bi file.
    I did pivot the raw data in excel as follow:

    Thank you Vishesh Jain for your time!


    ------------------------------
    beckmann Jim
    ------------------------------

    Conference-PBI_200x200


  • 10.  RE: sum total only once

    Posted 4 days ago
    Hi Vishesh Jain,

    Thanks so much for the updated solution. I have tried many times based on your attached files and got incorrect summed dollars. What I did just import the Sample.xlsx you attached in the attached pbix. And renamed the new data source table " Indicator Detail Only Count FS Beckmann " replaced the NULL to 0s. Duplicated your measure and named " Total sum " and placed it into the table graph. You can see the total 577.50, not sure where did it sum to become 577.50. The correct amount should be 660. In addition I lost the interaction filter between Agency slicer and Attribute slicer. I am enclosing the files I have downloaded from your attachments 3 days ago.
    Please advise and let me know what else I need to do.
    Danke und Einen schönen Tag noch!

    Beckmann



    ------------------------------
    beckmann Jim
    ------------------------------

    Attachment(s)

    xlsx
    sample5.xlsx   99K 1 version
    pbix
    Sum Total Once1.pbix   154K 1 version
    Conference-PBI_200x200


  • 11.  RE: sum total only once

    Top Contributor
    Posted 4 days ago
    Hi @beckmann Jim​,

    As I said in my EDITED response to ignore the message. I just realized after sending the message that the calculation was wrong.

    I was trying to add the values and then divide it by 2, which was on the assumption that both Universal Access and Intensive Case would have the same value, but my code was dividing other values as well.
    So the reason you are getting 577.7 is
    ((495+495)/2) + 165/2
    495 + 82.5
    577.5

    I know that this answer is wrong and it is not what you are looking for.
    I am trying to come up with a solution and will let you know once I have it.

    My apologies, I couldn't be of much help here.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Conference-PBI_200x200


  • 12.  RE: sum total only once

    Posted 3 days ago
    Hi Vishesh Jain,

    I am really grateful for your help up to this point. You are a great guy and having a compassion for helping others, appreciated !!

    Beckmann

    ------------------------------
    beckmann Jim
    ------------------------------

    Conference-PBI_200x200


  • 13.  RE: sum total only once

    Top Contributor
    Posted 3 days ago
      |   view attached
    Hi @beckmann Jim,

    Thank you for those overwhelming and kind words!​ Just trying to help out people who are where I was and in turn learn something new myself.

    Hopefully this solution will work for you.

    Please create the following CALCULATED COLUMN in your table, this will check which ID has how many programs.
    #Prog = CALCULATE( DISTINCTCOUNT('Indicator Detail Only Count FS'[ProgramName]), ALLEXCEPT('Indicator Detail Only Count FS', 'Indicator Detail Only Count FS'[ID]))​

    Then you can use either of the measures.
    Measure 1 named Total is ignoring the 'FS Intensive Case Management' when there are 2 programs for an ID.
    Measure 2 named Total 2 is taking the total of both programs for the ID and then dividing it by 2. This is on the assumption that the value in both the programs will be same.

    Total = 
    CALCULATE(SUM('Indicator Detail Only Count FS'[Value]), FILTER('Indicator Detail Only Count FS', 'Indicator Detail Only Count FS'[#Prog]=2 && 'Indicator Detail Only Count FS'[ProgramName] = "FS Universal Access"))
    +
    CALCULATE(SUM('Indicator Detail Only Count FS'[Value]), FILTER('Indicator Detail Only Count FS', 'Indicator Detail Only Count FS'[#Prog]<>2))​
    Total 2 = 
    CALCULATE(SUM('Indicator Detail Only Count FS'[Value]), FILTER('Indicator Detail Only Count FS', 'Indicator Detail Only Count FS'[#Prog]=2))/2
    +
    CALCULATE(SUM('Indicator Detail Only Count FS'[Value]), FILTER('Indicator Detail Only Count FS', 'Indicator Detail Only Count FS'[#Prog]<>2))


    Here is a screenshot of my result.

    I have attached my file for your reference.

    Hope this helps solve your problem.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Attachment(s)

    pbix
    Sum Total Once.pbix   108K 1 version
    Conference-PBI_200x200


  • 14.  RE: sum total only once

    Posted 2 days ago
    Hi Vishesh Jain,

    It works indeed, great job Vishesh!

    I am learning it from you and hopefully I could help other people having issues in the future as well.

    Viel Glück!

    Beckmann

    ------------------------------
    beckmann Jim
    ------------------------------

    Conference-PBI_200x200


  • 15.  RE: sum total only once

    Top Contributor
    Posted 2 days ago
    Glad to be of assistance Jim!

    If your issue has been resolved, please mark a solution at the best answer, as it help other facing the same problem, reach a solution directly.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Conference-PBI_200x200


  • 16.  RE: sum total only once

    Posted 20 hours ago
    Hi Vishesh Jain,

    Do you received my reply regarding how to mark the best solution in this forum? I could not find the place to let me mark. Would you please walk me through to mark the solution?

    Thank you!
    Beckmann

    ------------------------------
    beckmann Jim
    ------------------------------

    Conference-PBI_200x200