cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fragan
Helper III
Helper III

Custom total calculation for one column value

Hey,

 

I have this table :

 

image.png

 

I made a matrix visual out of it :

 

image.png


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
Fragan
Helper III
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)
))

 

 

View solution in original post

9 REPLIES 9
Fragan
Helper III
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)
))

 

 

View solution in original post

amitchandak
Super User IV
Super User IV

@Fragan 

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!

@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 :

 

image.png

 

I want the 482 to be 241.

@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.

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

@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!

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

 

 

image.png

 

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

 

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

bheepatel
Resolver IV
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!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors