Total by Rows

[lokeshpatel]

Hello!

I have a particular situation where I am looking to get the totals by row. I have researched with limited help but still am stuck trying to cross the finish line. Below are the posts that I've tried. 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

It seems to be a common "problem". I understand it's in teh calculations. More on my problem.

I am looking at specific effective billing rates (ebr). They are a dollar amount divided by the amount of hours. From the picture below, you can see (A)/(B) gives the EBR (C). Even the past amounts are correct as they are a single value (D), (E), (F). The issue comes in when trying to obtain the max of budgeted hours (B) and past hours (E). This is the amount I would like to use as the denominator. 

As you can see, neither (G) nor (H) gives the correct "total" that I would like. Therefore, (I) is incorrect for just the total. It gives the correct row EBR. Furthermore, if the project is in-flight, I would like to use the max mentioned above. If it is completed, I would only like to use the past (E). 

The correct hours used should be 

Project1:	45.00
Project2:	25.00
Project3:	498.25
Project4:	113.00
Project5:	1,163.00
Total	1,844.25

I have tried these formulas:

Overall EBR var = 
Divide( [MFAB],
sumx( VALUES(new_XXXXXprojects), 
    if([EBR Project Status measure]="Completed",
        [Past Hours],
        calculate(max([BudgetedHours], [Past Hours])
            )
    )
))

SUMX-summarize = 

sumx( SUMMARIZE(new_XXXXXprojects, new_XXXXXprojects[XXXXX Project], "Newhours", 
    if(SELECTEDVALUE(opportunities[EBR Project Status]) = "Completed",
        [Past Hours],
        calculate(max([BudgetedHours], [Past Hours])
            ))
    ), [Newhours]
)

Also a variation of HASNOVALUE.

Any suggestions? Thank you in advance!

[v-jingzhang]

Hi @lokeshpatel 

Has this problem been solved? If so, kindly Accept the appropriate reply as the solution or post your own solution to help close this topic. More people will benefit from it. Otherwise if you are still confused about it, please provide more details about your problem. Thanks a lot.

Best Regards,
Community Support Team _ Jing Zhang

[lokeshpatel · Accepted solution]

Hello,

I wanted to confirm what I did to solve this problem. I ended up rolling the values up into the hierachy table. since there was a relationship problem, I could not make it flow through. I created a calculated column and attached it to the Projects. That gave me the actual total needed.

[v-jingzhang]

Hi @lokeshpatel 

Sorry I have some confusions about your measures, could you clarify it further to help me understand them better?

• Are [MFAB]/[EBR Project Status measure]/[Past Hours]/[BudgetedHours] all measures? If they are, could you share their formulas?
• How many tables are these data from? What is the relationship between them? I see at least two tables 'opportunities' and 'new_XXXXXprojects' are involved. 

I would appreciate it if you would provide some dummy data in table format (which removes sensitive information) so that I can copy it to my PBIX file to test the measures.

Best Regards,
Community Support Team _ Jing Zhang

[lokeshpatel]

Hi,

[MFAB]/[EBR Project Status measure]/[Past Hours]/[BudgetedHours] are all measures.

MFAB =

CALCULATE(
    SUM('new_productscheduleses'[Month Forecast Amount Base]),
    ALLSELECTED('new_productscheduleses'[Month Forecast Amount Base])
)

EBR Project Status measure = if( EOMONTH(TODAY(),-1) > max((opportunities[Max of Schedule Date and Product End Date])), "Completed", "In-flight")

Past Hours = CALCULATE(SUM('Summarized Time'[dailyhours]),DateForecast[Calendar RelativeMonthPos]>0)

BudgetedHours = CALCULATE(sum(TASKS[Budgeted Hours]))

Please let me know if you need additional information. Thank you very much!

[v-jingzhang]

Hi @lokeshpatel 

Please break down the measures like below. When you combined all of them into a measure to calculate the total value, the MAX() function will get the maximum value based on all underlying data, thus the total is not the sum of row values in the table visual. This is probably the cause of the wrong total value.

HoursforOverallEBR =
IF (
    [EBR Project Status measure] = "Completed",
    [Past Hours],
    CALCULATE ( MAX ( [Budgeted Hours], [Past Hours] ) )
)

HoursforOverallEBR_Total =
SUMX ( VALUES ( 'Table'[Project] ), [HoursforOverallEBR] )

Overall EBR var =
DIVIDE ( SUM ( 'Table'[Total Forecast] ), [HoursforOverallEBR_Total] )

 

I simplified the data model in my demo so you will need to adjust the measures, for details please download the PBIX file from here. Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.