cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

1 ACCEPTED SOLUTION
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
Community Support
Community Support

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.

View solution in original post

Super User IV
Super User IV

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

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors