Forum Discussion

sakBI's avatar
sakBI
Frequent Visitor
3 years ago
Solved

Regrouping costs per level

Dear community,

I'm trying to build a Power BI report to do Azure Cost recovery.

We have 2 kinds of customers:

  • Level 1 Use Azure
  • Level 2 Use Azure + Consultancy services (a percentage on the top of the consumption)

From the EA portal I get the billing information, it provides the cost and consumption related to the Customer Subscription.

I have a table named "Customer Level" where I relate those subscriptions to the support level they have

The issue comes when trying to do the cost recovery, as for Level 1 and Level 2 customers I recover the Azure Usage, but for the Level 2 I recover the consultancy services.

I wanted to build a table that had one line for the Constumption and another one for the Consultancy services. 

I'm able to make it in 2 columns but I'm don't know how to create a column that uses the value depending on the level.

The main issue is that Level 2 customers are charged for both lines .

I know Is very complex to explain and I can't provide a data sample .

 

Need Help
Tips and Tricks
  • sakBI's avatar
    sakBI
    3 years ago

    Sorry,

    I found it very difficult to explain even to my colleagues, but this formula worked finally .
    _Applicable = IF ( SELECTEDVALUE(CostRecovery[SD]) < 1 , CALCULATE(SUM(Usage_details_Bill[L1_Cost]),CostRecovery[Service ID] = "SC-DCT-001"),CALCULATE(SUM(Usage_details_Bill[L2_Cost]),CostRecovery[Service ID] = "SC-DCT-006"))

3 Replies

  • sakBI's avatar
    sakBI
    Frequent Visitor
    3 years ago

    Sorry,

    I found it very difficult to explain even to my colleagues, but this formula worked finally .
    _Applicable = IF ( SELECTEDVALUE(CostRecovery[SD]) < 1 , CALCULATE(SUM(Usage_details_Bill[L1_Cost]),CostRecovery[Service ID] = "SC-DCT-001"),CALCULATE(SUM(Usage_details_Bill[L2_Cost]),CostRecovery[Service ID] = "SC-DCT-006"))

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi sakBI 

    I don't quite understand what you're trying to achieve, can you elaborate a little more about your problem? Even if you can't provide a sample, can you provide some input for example and your expected output?

    Best Regards!

    Yolo Zhu

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

  • sakBI's avatar
    sakBI
    Frequent Visitor
    3 years ago

    Thanks

    I will try to provide more details.

    I have a with Customer name, Cost, L1 Cost and L2 Cost

    For the DCT-001 Services , the cost should be L1 cost of all customers

    For the DCT-006 , the cost should be the L2 Cost of those that have it.

    I tried with :

    _Bill = IF(
        SELECTEDVALUE(CostRecovery[Service ID]) = "SC-DCT-001",
        CAlculate('Usage details'[#Bill_L1]),
        IF(
            SELECTEDVALUE(CostRecovery[Service ID]) = "SC-DCT-006",
            CAlculate('Usage details'[#Bill_L2]),
            BLANK()
        )
    )
    But the results I get are not filtered by the customer.