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

Pulling my Hair Out...... SUMX and Divide

  • 1.  Pulling my Hair Out...... SUMX and Divide

    Posted Feb 12, 2019 09:39 AM
    Hello All,

    New to Power Bi and the group.  I've been working on this visualization and I'm stuck on a particular metric and it is driving me insane.  I'm sure it is a simple mistake, but wanted to get some help to fix what is going on.  I created the following measure to start so I can ultimately find out what the Review % is.

    TotalDocs = sumx(Metrics, Metrics[Document 1] + Metrics[Document 2] + Metrics[Document 3] + Metrics[Document 4] + Metrics[Document 5] + Metrics[Documents for Review])

    Formula I'm struggling with is:

    Review % = SUMX(Metrics, DIVIDE(Metrics[Documents for Review], [TotalDocs], 0))

    Review%I've at least figured out that it is adding the Review % each day instead of calculating the DocumentsforReview/TotalDocs.  Anybody out there that can set me straight?

    Data set

    Name Today's Date? Document 1 Document 2 Document 3 Document 4 Document 5 Documents for Review
    Employee 1 1/2/2019 48 46 0 16 10 5
    Employee 2 1/3/2019 122 43 0 5 2 0
    Employee 3 1/3/2019 144 51 0 31 7 6
    Employee 1 1/3/2019 83 25 0 24 6 10
    Employee 1 1/4/2019 164 36 0 5 14 0
    Employee 2 1/4/2019 16 1 0 15 1 0
    Employee 3 1/4/2019 89 91 1 13 1 5
    Employee 4 1/4/2019 138 70 0 26 18 11


    ------------------------------
    Thanks for your help,

    Kenny


    ------------------------------


  • 2.  RE: Pulling my Hair Out...... SUMX and Divide

    Gold Contributor
    Posted Feb 12, 2019 12:27 PM
      |   view attached
    ​You can't use the SUMX, because you are telling DAX to Sum them when there is no row context.
    First thing, your totalDocs could be written as TotalDocs = [Document 1]+[Document 2]+[Document 3]+[Document 4]+[Document 5] if you create a measure to sum each document.
    Second, because you are working with the sum measures, you could just write % Review Individual = DIVIDE([Documents for Review],[TotalDocs],0). Keep in mind you have created a measure called [Documents for Review] as well.​

    I have attached a simple version based on your provided information.


    ------------------------------
    Brian Connelly
    Sr. Associate, RSM US
    Northeast Business Intelligence Practice
    ------------------------------

    Attachment(s)

    pbix
    Doc Sample.pbix   51K 1 version


  • 3.  RE: Pulling my Hair Out...... SUMX and Divide

    Posted Feb 13, 2019 11:29 AM
    Brian,

    Thank you for the help.  I knew I was doing something wrong.

    KL

    ------------------------------
    Kenny
    ------------------------------