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 an internal SharePoint site, that has filable form. 

Once the user enters in the money collected. sharepoint adds up the total and puts in into a calculated colmun called "Total".

 

The field that says "Took on behalf of" is a personal look up.

 

What I am looking for, is a measure in Power BI, that if the coumn "Took on Behalf of" = someone's name (because it can be blank) take 80% off of this entries total, and add it to another coulmn.

 

If I explain why we do this, maybe it will help you understand what I am trying to accomplish. 


Each time a user takes a payment, they record it in sharepoint using this form. If the user is taken a payment for a client that belongs to another user, they get 20% of it, and the user who the client belongs to gets 80% of it. 

 

Using this measure, I managed to get 80% of the total cash. So I assume some kind of if statement will be involved, as well as a colmn for storing data to be added to each person's total.

 

Thanks in advance! 

William 

 

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

14 Replies

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