Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculations based on Conditional Calculated columns

I have a table that splits agents into productive and non-productive minutes using a calculated column.

 

The table visual can perform this split quite well, however, when I want to do calculations based on those split values, it becomes quite difficult.

 

So this is what I have in Power BI:

 

 

The columns you can see are derived from a calculated column 'ProductiveSplit' 

The calculation for the 'ProductiveSplit'  column is something like:

ProductiveSplit = IF('PBI DAST'[AgentStateTypeIdent]=117,"NP-Known"
,IF('PBI DAST'[AgentStateTypeIdent]=120,"NP-Unknown"
,IF('PBI DAST'[AgentStateTypeIdent]=124,"NP-Known"...

 

What I need are 2 more columns as illustrated in the image above. The calculations are based on the row values of the calculated column 'ProductiveSplit' 

 

So, Prod% = Prod / (NP-Known + NP-Unknown + Prod)
      Known% = (Prod + NP-Known) / (NP-Known+ NP-Unknown + Prod)

 

I have the excel sheet attached of how I need the results to appear below.

 

Excel

 

Any help would be great.

Need Help
Show and Tell
  • v-xicai's avatar
    v-xicai
    5 years ago

    Hi Anonymous ,

     

    You may create measures like DAX below.

     

    Prod% =
VAR _Prod =
    CALCULATE (
        SUM ( Table1[PeriodMins] ),
        FILTER ( Table1, Table1[ProductiveSplit] = "Prod" )
    )
VAR _Total =
    CALCULATE ( SUM ( Table1[PeriodMins] ), ALLSELECTED ( Table1 ) )
RETURN
    DIVIDE ( _Prod, _Total )




Known% =
VAR _Prod =
    CALCULATE (
        SUM ( Table1[PeriodMins] ),
        FILTER ( Table1, Table1[ProductiveSplit] = "Prod" )
    )
VAR _Known =
    CALCULATE (
        SUM ( Table1[PeriodMins] ),
        FILTER ( Table1, Table1[ProductiveSplit] = "NP-Known" )
    )
VAR _Total =
    CALCULATE ( SUM ( Table1[PeriodMins] ), ALLSELECTED ( Table1 ) )
RETURN
    DIVIDE ( _Prod + _Total, _Total )

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , if you want hybrid table like the one not possible. I logged an idea

    https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

     

     

     

    There is % wise total option in matrix , column-wise too. right click on periodmins under values and check

    refer if those can help

    https://www.fourmoo.com/2017/07/18/power-bi-dax-measures-for-excel-based-of-column-total-or-of-row-total/

     

    or you can create a new meausre

    divide(sum(table[period mins]),calculate(sum(table[period mins]). allexcept(Table,table[productivesplit])))

     

    or

     

    divide(([period mins]),calculate(([period mins]). allexcept(Table,table[productivesplit])))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks  amitchandak for your reply.

       

      This does kind of work, if I display values as %s. However, I have extra columns that I dont need to show, how do I get rid of them (marked x)?

       

       

      Also, how can I get the 2nd calculation/column:  Known% = (Prod + NP-Known) / (NP-Known+ NP-Unknown + Prod) ?

  • v-xicai's avatar
    v-xicai
    Community Support
    5 years ago

    Hi Anonymous ,

     

    You may create measures like DAX below.

     

    Prod% =
VAR _Prod =
    CALCULATE (
        SUM ( Table1[PeriodMins] ),
        FILTER ( Table1, Table1[ProductiveSplit] = "Prod" )
    )
VAR _Total =
    CALCULATE ( SUM ( Table1[PeriodMins] ), ALLSELECTED ( Table1 ) )
RETURN
    DIVIDE ( _Prod, _Total )




Known% =
VAR _Prod =
    CALCULATE (
        SUM ( Table1[PeriodMins] ),
        FILTER ( Table1, Table1[ProductiveSplit] = "Prod" )
    )
VAR _Known =
    CALCULATE (
        SUM ( Table1[PeriodMins] ),
        FILTER ( Table1, Table1[ProductiveSplit] = "NP-Known" )
    )
VAR _Total =
    CALCULATE ( SUM ( Table1[PeriodMins] ), ALLSELECTED ( Table1 ) )
RETURN
    DIVIDE ( _Prod + _Total, _Total )

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.