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.
Hi there,
I have a table that contains Accounts, OpenDate, and NumberofProds. NumberofProds is calculated as MAXX(tablename, tablename[OpenProds]. And i am now trying to get a sum of all NumberofProds for the whole table. Your help is appreciated.
sample:
Accounts OpenDate NumberofProds
1 11/12/2019 2
2 11/13/2019 1
3 11/13/2019 3
Total_NumofProds = 6
if i enable the total for the table, the total showing the maximum row value.
Thank you
Glen
Solved! Go to Solution.
Hi gco,
You could try below measure to see whether it work or not
My sample data is like below
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMLTUN9Q3BDJNlWJ1MASNUAWNwIKG2ASNUQWNwYJm2AQh2pOw2Z6EzfYkbLajCaJpR7Y9CcP2WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, date = _t, amount = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"date", type date}, {"amount", Int64.Type}})
in
#"Changed Type"
Measure 4 =
IF (
HASONEVALUE ( 'Table (3)'[id] ),
MAXX ( 'Table (3)', 'Table (3)'[amount] ),
SUMX (
SUMMARIZE (
'Table (3)',
'Table (3)'[id],
'Table (3)'[date],
"max", MAX ( 'Table (3)'[amount] )
),
[max]
)
)
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi gco,
You could try below measure to see whether it work or not
My sample data is like below
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMLTUN9Q3BDJNlWJ1MASNUAWNwIKG2ASNUQWNwYJm2AQh2pOw2Z6EzfYkbLajCaJpR7Y9CcP2WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, date = _t, amount = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"date", type date}, {"amount", Int64.Type}})
in
#"Changed Type"
Measure 4 =
IF (
HASONEVALUE ( 'Table (3)'[id] ),
MAXX ( 'Table (3)', 'Table (3)'[amount] ),
SUMX (
SUMMARIZE (
'Table (3)',
'Table (3)'[id],
'Table (3)'[date],
"max", MAX ( 'Table (3)'[amount] )
),
[max]
)
)
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @dax and @Anonymous ,
Thanks to both of you. It seems like Zoe's (@dax) measure gave me the most accurate result. Now i just have to learn how the measure is doing the calculations so that i understand the logic better! Thanks again
Glen
Hi @gco ,
the only way I can suggest:
NumberofProdsSum =
var MaxTable = GROUPBY('Table', [Accounts], [OpenDate], "NOP", MAXX(CURRENTGROUP(), [OpenProds]))
return SUMX( MaxTable, [NOP])
This returns correct numbers for each row and total. The downside of the formula, I think, is that you need to group it to the exact columns in the resulting table visual.
Kind regards,
JB
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.