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 comes at a monthly cost.

In each month, I have an amount of each and need to know the total monthly cost.

*The crossed out column is the ID of each product.

JMS1985_0-1606406751739.png

Here's the table I want to reflect:

JMS1985_1-1606406816479.png

*Total shows: average units (correct), month quota (Correct per line, incorrect in total).

The individual fee for each ID works out for me, but the problem I have with the total... The sum of the individual amounts does not give me the total sum (I know why it is but I do not know how to solve it).

The problem I have in the measures I use:

Fee s/he/he/they SUM([Daily Fee])*[units]
Monthly fee [Quota]*COUNT('Invoking Function'[Date])

I basically need to multiply the Daily Quota by the number of units. However, it only lets me multiply if I add a function in front of Daily Quota, in this case it is SUM. Therefore, in the total, it performs the sum of all the odds and multiplies it by the average units.... this gives me a higher value than I should.

Any ideas? it's probably an extremely easy thing, but I'm pretty new to PowerBi

Thanks a lot

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

2 Replies

  • 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])

     

     

  • v-yingjl's avatar
    v-yingjl
    Community Support
    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.