Forum Discussion

voonwah's avatar
voonwah
Frequent Visitor
5 years ago
Solved

help needed on calculation

Hi All,

 

I'm new to PowerBi and would seek help on how to perform the calculation as i have fixed quota limit for all department except on "Department F"

 

What i need to display is the "Actual Usage" - "Quota Limit" to know the monthly extra usage on each department.

 

Can someone please advise?

 

 

Thanks,

Voon Wah

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi, voonwah ;

    You could create a measure .

    extra usage =
IF (
    MAX ( [Base Package] ) = "DepartmentF",
    BLANK (),
    CALCULATE (
        SUM ( [ActualUsage] ) - SUM ( [QuotaLimit] ),
        ALLEXCEPT ( 'Table', 'Table'[Base Package] )))

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    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
    5 years ago

    voonwah , what is you based data,

     

    This would be like

    Sumx(values(Table[department]) ,calculate(max(table[Quota]) -sum(Table[Usages])))

     

    or

     

    Sumx(summarize(Table, Table[department], table[month year], "_1" ,calculate(max(table[Quota]) -sum(Table[Usages]))),[_1])

  • Arul's avatar
    Arul
    Super User
    5 years ago

    Hi voonwah , 

    Create calculated column by using the below formula.

    Extra usage = 'Table'[Actual Usage]-'Table'[Quota Limit]

     

    Thanks,

    -Arul

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    Hi, voonwah ;

    You could create a measure .

    extra usage =
IF (
    MAX ( [Base Package] ) = "DepartmentF",
    BLANK (),
    CALCULATE (
        SUM ( [ActualUsage] ) - SUM ( [QuotaLimit] ),
        ALLEXCEPT ( 'Table', 'Table'[Base Package] )))

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.