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

 

Hello again, below is some test data I entered. 

 

Collections, Split Fee and Consult fee are added to get a total. Then Sharepoint will split the amount by 80% and 20% if "took on behalf" is populated. and populate the Fields "other person is gets" with 80%, and "total" with the 20%

 

Using your formula, I am able to get the below data. Now although the data is split correctly, I am looking for a way to apply the 80% that was calculated and apply it to the person that is in the "Took on Behalf of" field. So in the below example, we would want $80 of the "Other Person Gets" ($152) to go to Peter and the rest ($72) to go to Tokay. 


The data is going to be presented in simple bar graphs, like below just with one value not two. (which will be their total, + the total of "Other person Gets")

 I am essentially at the point, where I need to find a way to make this excel formula work in DAX.

rachaelnelson's avatar
rachaelnelson
Frequent Visitor
9 years ago

I'm not sure if you need to create both of these calculated columns but if you do, here are the formulas.

 

OtherPersonGets = IF(DailyCashReport[TookOnBehalfOf]<>"",(DailyCashReport[Collections]+DailyCashReport[Split-Fee Payment]+DailyCashReport[Consult Fee])*.8,DailyCashReport[Collections]+DailyCashReport[Split-Fee Payment]+DailyCashReport[Consult Fee]

Total = DailyCashReport[Collections]+DailyCashReport[Split-Fee Payment]+DailyCashReport[Consult Fee]

 

Let me know if it works. I didn't enter the dataset into my BI.

  • fodelement's avatar
    fodelement
    Frequent Visitor
    9 years ago

    Good Morning,

     

    Thank you so much for all your help, I do appreciate it.

     

    Sharepoint already calculates those fields for me.

     

     

     

     

     

    The end result will be a bar graph for each user. It will only have one value. That value will be:

     

     "Total" + "Other Person Gets"

     

    The issue I cannot figure out is, "Other person gets" needs to be only if the "took on behalf of" = the person.

     

    So for each instance that "took on behalf of" is = to "William" SUM those and then add them to "Total" to get a single value.


    The total amount they took in for themselves, and the total amount other people took in for them. (The 80% calculation that you helped with already)

     

    Sorry if I am not being clear, I am just flustered with all of this. :)

     

    This is as simple as I can make it.

    (Using excel that is)

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

      Hi fodelement,

      Please add an index column in Power Query Edit by clicking "add column"->index column, please see following screenshot.



      Then create a calculated column using the formula.

      Column = IF(Table3[Person]= LOOKUPVALUE(Table3[Took on behalf],Table3[Index],Table3[Index]-1),LOOKUPVALUE(Table3[They get],Table3[Index],Table3[Index]-1)+Table3[I get],Table3[I get])


      You will get the expected result.



      Finally, create a bar chaart using the new column as follows.



      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.