Forum Discussion

fodelement's avatar
fodelement
Frequent Visitor
9 years ago
Solved

Reduce by percent if column value equals something.

Hello all,   I am new to Power BI, and thanks to the helpful posts on here, I managed to catch up pretty quickly, so thanks for that!   One issue I cannot find an answer to is as follows. I have ...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi fodelement,

    Please create a new table by clicking New table under Modeling on Home page. Then type the formula to create a new table

    NewTable=UNION(SELECTCOLUMNS(Table,"Name",Table[CreatedBy],"Value",table[Total]),SELECTCOLUMNS(FILTER(Table, Table[Index]=CALCULATE(MIN(Table[Index]),ALLEXCEPT(Table,Table[Took on Behalf of]))),"Name",Table[Took on Behalf of],"Value",Table[OtherPersonGets]))

    Then add the Name as Axis, the sum(value) as value fields.

    Best Regards,
    Angelia

fodelement's avatar
fodelement
Frequent Visitor
9 years ago

Angelia, thank you very much, this is as close as I have been to getting this to work.

 

TEST = IF('Daily Cash Report'[Author.FieldValuesAsText.Title]= LOOKUPVALUE('Daily Cash Report'[Took on Behalf_x0020.Title],'Daily Cash Report'[Index],'Daily Cash Report'[Index]-1),LOOKUPVALUE('Daily Cash Report'[Other Person Gets],'Daily Cash Report'[Index],'Daily Cash Report'[Index]-1)+'Daily Cash Report'[Total],'Daily Cash Report'[Total])

 


My only issue now, (which I hope is not a big one) is that when the person enters more than one entry, it stops adding "Other Person Gets".

 

 

Also, if the person did not create an entry, and their name yet to apear on the 'Daily Cash Report'[Author.FieldValuesAsText.Title] column, they do not appear.

 

Not sure if there is anything we can do to make that work.

 

I am thinking using excel to pull the connection from sharepoint, do the math, and then have power BI import it from the spreadsheet may be the only way to get this to work.

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi fodelement,

Please create a new table by clicking New table under Modeling on Home page. Then type the formula to create a new table

NewTable=UNION(SELECTCOLUMNS(Table,"Name",Table[CreatedBy],"Value",table[Total]),SELECTCOLUMNS(FILTER(Table, Table[Index]=CALCULATE(MIN(Table[Index]),ALLEXCEPT(Table,Table[Took on Behalf of]))),"Name",Table[Took on Behalf of],"Value",Table[OtherPersonGets]))

Then add the Name as Axis, the sum(value) as value fields.

Best Regards,
Angelia

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi fodelement,

    SELECTCOLUMNS function returns a table including a column which satisfy the expression. UNION creates a join table of a pair of tables.

    Best Regards,
    Angelia

  • fodelement's avatar
    fodelement
    Frequent Visitor
    9 years ago

    Hello again v-huizhn-msft

     

    I started to test this with real data and found an issue. So here is the code.

     

    NewTable = UNION(SELECTCOLUMNS('Daily Cash Report',"Name",'Daily Cash Report'[Author.FieldValuesAsText.Title], "office", 'Daily Cash Report'[Office.Office], "date", 'Daily Cash Report'[Date], "Value",'Daily Cash Report'[Total]),SELECTCOLUMNS(FILTER('Daily Cash Report', 'Daily Cash Report'[Index]=CALCULATE(MIN('Daily Cash Report'[Index]),ALLEXCEPT('Daily Cash Report','Daily Cash Report'[Took on Behalf_x0020.Title]))),"Name",'Daily Cash Report'[Took on Behalf_x0020.Title], "office", 'Daily Cash Report'[Office.Office], "date", 'Daily Cash Report'[Date], "Value",'Daily Cash Report'[Other Person Gets]))

    Here is the Sharepoint Data

     

    Sharepoint

     

    Here is the "New Table" that you had me create.

    Power Bi "NewTable"

    Now, it successfully pulls and applies the correct amount (80%) that the person deserves, and it even puts it on the graph as I wanted. The issue is, if the person's name shows up twice, (such as the sharepoint data above), it only puts the first entry from the "took on behalf" column.

     

    Below is the data being pulled into sharepoint, or "Daily cash Report" in the code.

     

     

    If you notice in "New Table", it does however show the creators name multiple times. So I just need it to do the same thing for the "took on behlaf" of person as well.

  • fodelement's avatar
    fodelement
    Frequent Visitor
    9 years ago

    I think I am just consued about how the 

    UNION(SELECTCOLUMNS

    code works.  

     

    Everytime I try to make changes, it ends up getting worse. 

  • fodelement's avatar
    fodelement
    Frequent Visitor
    9 years ago
    v-huizhn-msft , as always thank you for your insite. So in the code I posted, do you have an idea why it that's preventing the other person's name to be added to the column more than once?

    In reality, if the other person's name is not added to the list again but their total is increased that'll be just fine. But in its current state once someone is added to the column via "taken on behalf of", they are not added again and their total dollar amount does not increase.
  • fodelement's avatar
    fodelement
    Frequent Visitor
    8 years ago

    Hello all!!

     

    I still need help on this, if someone can take a look for me. :)


    So here is a little bump.

     

    Thanks!