Forum Discussion

JMS1985's avatar
JMS1985
Frequent Visitor
5 years ago
Solved

Cost calculation

Hello I've been trying to get something pretty simple in excel for a while, but I've been resisting it in PowerBi... see if anyone can help me 🙂 I have a number of products and each of them ...
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hello @JMS1985 ,

    This looks like a totals of measure problem. Very common. See this post about it
    https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

    In addition, this Quick Measure, Measure Totals, The Final Word should get what you need:
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

    In this case, you can create another measure like this to set the total value in the array:

    Unit_avg = AVERAGE('Table'[Units])
    Cuota = 
VAR tab =
    ADDCOLUMNS (
        'Table',
        "avg_unit",
            CALCULATE (
                AVERAGE ( 'Table'[Units] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[ID] = EARLIER ( 'Table'[ID] )
                        && 'Table'[Date]. [MonthNo] = EARLIER ( 'Table'[Date].[ MonthNo] )
                )
            ),
        "v",
            CALCULATE (
                SUM ( 'Table'[value] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[ID] = EARLIER ( 'Table'[ID] )
                        && 'Table'[Date]. [MonthNo] = EARLIER ( 'Table'[Date].[ MonthNo] )
                )
            )
                * CALCULATE (
                    AVERAGE ( 'Table'[Units] ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[ID] = EARLIER ( 'Table'[ID] )
                            && 'Table'[Date]. [MonthNo] = EARLIER ( 'Table'[Date].[ MonthNo] )
                    )
                )
    )
VAR tb =
    SUMMARIZE ( tab, [ID], [v] )
RETURN
    IF (
        ISINSCOPE ( 'Table'[ID] ),
        SUM ( 'Table'[value] ) * [Unit_avg],
        SUMX ( tb, [v] )
    )

    cost.png

    Attached a sample file in the next one, hopes to help you.

    Best regards
    Community Support Team _ Yingjie Li
    If this post helps,then consider Accepting it as the solution to help other members find it faster.

amitchandak's avatar
amitchandak
Super User
5 years ago

JMS1985 , Not very clear. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Now in case you need multiply you need to have that line level 

A column = [Daily Fee]*[units]

or Measure a line-level  sumx(Table,[Daily Fee]*[units])

if not possible then push measure on the lower level

here both [Daily Fee], [units] are measures

 

Divide should always be a measure

divide(Sum(Table[A]), Sum(Table[B]))

 

sumx(values(Table[ID]),[Daily Fee]*[units])