Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey,
I have this table :
I made a matrix visual out of it :
What i want to do is change the totals to :
I want to change the sub-totals and grand total for one attribute.
Anyone can help me with that please?
Solved! Go to Solution.
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)
))
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)
))
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])
@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.
@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
@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)
I mean what im trying to do is to change the column subtotal of Attribute2 to (ColumnSubtotal/2)
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |