Cost calculation

[JMS1985]

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.

Here's the table I want to reflect:

*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 · Accepted solution]

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

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]

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