Modify Grand Total with other value

[dobregon]

Imagine that i have a table with measures like (max name, max project, max bla bla and other with values). imagine 5 values.. that if i sum the values is 50, but i want to do the sum in the table for the subtotals but for the grand total i want to force a 100.

it could be possible?

[MacarenaGB]

Hello!

I have a similar case but with averaex in between and I don't know if you can help me.

I have a series of expenses with the same description over the months and I want to show the average cost of each cat and that the total shows the sum of the averages (17.27 in this case)

Any help?

A thousand thanks

[v-yingjl]

Hi @dobregon ,

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Yingjie Li

[dobregon]

@v-yingjl yes, you are right. this is a better example

Data example

Lv 001	lv 002	Value
Motor	motorbike	5000
Motor	car	20000
Motor	bus	50000
street	mountainbike	2000
water	boat	100000
water	surf	2000

With that, i will obtain a something like this (examples with pivots in excel)

But i dont want to do the tipical sum, i want to have different values depends of it is row value, subtotal and grand total: 
- For row values i do a sum of values. no problem here
- For the subtotal, instead of the sum of the values i want to have the % of the total. To do that i have used the countrows (always a level need to have more than 1 lv002. So, i do something like, if countrows > 1, calculation of %, if not, sumrows

 In that part, is fine. the problem is that i want to change the grand total for other value and i cant use the countrows logical. I'm asking if it is possible or exist something like.. if it is grand total.. put then if not.. do this

Thanks!

[v-yingjl]

Hi @dobregon ,

I have create a sample and tried to get it based on your sample data:

The Force total measure is like this:

Force total =
IF ( NOT ( ISFILTERED ( 'Table'[Lv 001] ) ), 100, SUM ( 'Table'[Value] ) )

The value '100' is the value which you want to show in the total bar and you can change it manually.

The Total measure is like this as a comparison:

Total =
CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table' ) )

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.