cancel
Showing results for
Did you mean:
Helper III

## Custom total calculation for one column value

Hey,

I have this table :

I made a matrix visual out of it :

What i want to do is change the totals to :

• For  Attribute1 keep the totals
• For Attribute2, totals = totals/2

I want to change the sub-totals and grand total for one attribute.

Anyone can help me with that please?

1 ACCEPTED SOLUTION
Helper III

I found a solution :

``````Mesure = SUMX( 'Data'; SWITCH('Data'[attributes];
"Attribut2";IF(HASONEVALUE('Data'[date]);CALCULATE(SUM(Data[value]);Data[attributes] = "Attribut2");CALCULATE(SUM(Data[value]);Data[attributes] = "Attribut2")/2);
var a = 'Data'[attributes] return
CALCULATE ( SUM ( 'Data'[value] );'Data'[attributes]=a)
))``````

9 REPLIES 9
Helper III

I found a solution :

``````Mesure = SUMX( 'Data'; SWITCH('Data'[attributes];
"Attribut2";IF(HASONEVALUE('Data'[date]);CALCULATE(SUM(Data[value]);Data[attributes] = "Attribut2");CALCULATE(SUM(Data[value]);Data[attributes] = "Attribut2")/2);
var a = 'Data'[attributes] return
CALCULATE ( SUM ( 'Data'[value] );'Data'[attributes]=a)
))``````

Super User IV

Create a measure like that and using hasonevalue or isfiltered , use that for GT

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

something like this

GT Measure =sumx(Summarize(Table,Table[Attribute],"_1", sumx(Table,if(Table[Attribute] ="Attribute2" ,Table[Value]/2,Table[Value]))),[_1])

if(isfiltered(Table[Attribute]),sum(Table[Value]),[GT Measure])

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!

Helper III

@bheepatel with your solution all Attribute2 values will be divided by 12

@amitchandak the idea seem good, but i want also the sub total of attribute2 to change :

I want the 482 to be 241.

Resolver IV

@Fragan Apologies - I meant to write out 2, not 12. The measure would be as below:

NewValue = IF(Attributes = "Attribute2", Value / 2, Value)

The above column will give you the values where if the Attribute is Attribute2, then it will divide the original value by 2. If it is not Attribute2, it will keep the original value as is. This should change your sub-totals and totals.

Helper III

It changes the totals and the values of each Attribute2, I just want the line totals and columns totals to be changed

Super User IV

@Fragan , Sure You can share

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!

Helper III

@amitchandak  https://easyupload.io/0a178t , as you can see your solution is working but only for line total :

I want it to work for column total too, (i.e: the 482 should become 241)

Helper III

I mean what im trying to do is to change the column subtotal of Attribute2 to (ColumnSubtotal/2)

Resolver IV

Hi @Fragan

In your first Table, you can add an additional column with the following formula:

NewValue = IF(Attributes = "Attribute2", Value /12, Value)

The above column will give you the values where if the Attribute is Attribute2, then it will divide the original value by 12. If it is not Attribute2, it will keep the original value as is. This should change your sub-totals and totals.

You can use the NewValue column in your matrix visual.

Hope that helps!

Announcements

#### Manage your user group events

Check out the News & Announcements to learn more.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

#### Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors