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

rachaelnelson's avatar
rachaelnelson
Frequent Visitor
9 years ago

Create a calculated column as follows:

 

CollectedCalculation = IF(DailyCashReport[TookOnBehalfOf]<>"",DailyCashReport[Total]*.8,DailyCashReport[Total])

 

 

  • fodelement's avatar
    fodelement
    Frequent Visitor
    9 years ago

    Hello,

     

    Thank you for your response. This successfully removes the 80% from the total only if the field has a name in it. So I made a new column that calculates the difference between the two so I can apply the 20% to the person who took the payment.

    So thank you for that!

    The question that remains now is, how can I make power BI apply the 80% removed (648.64) to the person's name that is in the column (took on behalf of)?

     

     

     

     

    • rachaelnelson's avatar
      rachaelnelson
      Frequent Visitor
      9 years ago

      Can you send me a few rows of your dataset that is being used?  Are you trying to create a matrix with this data? Or what type of visual are you trying to create?

       

      I could see the following but this might not be what you are trying to accomplish.

       

      Rep           Total Collected for Self      Total Collected by other Rep         Total Collected for Other Rep

      Name1         100% of Total                    80% of Total                                  20% of Total

      Name2 ...

       

       

      • 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.