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.
Hello everyone,
Yesterda we received a new data format which basically sends us category total instead of competitors being logged as "Others". Basically before it was like this;
Product: 15€
Product: 2€
Others: 3€
Category Total: 20€
The data we receive now is without the "Others" and looks like this;
Product: 15€
Product: 2€
Category Total: 20€
This is beyond frustrating and I am trying hard to think of an easy solution to calculate what "Others" are valued at. I am trying to come up with formula that will replace "Category Total" with "Others" and leave Product 1 and 2 alone. I am trying to come up with a formula but something seems amiss;
==IF('data'[brand]="Others";CALCULATE(SUM('data'[€]);'data'[brand] = "Others")- CALCULATE(SUM('data'[€]);'data'[brand] <> "Others"); CALCULATE('data'[€];'data'[brand]<>"Others"))
Can anyone help? This issue's been consuming me for a whole week and I'll appreciate any help I can get.
Solved! Go to Solution.
I assumed your input structure and here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WclTSUQooyk8pTS4xBDKNlWJ1UASNgExTuKBzYklqen5RZUh+SWIOkG8IkXJCqDcGMk3QBU3ghjhhMwRoaSwA",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Category = _t, Product = _t, Value = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Category", type text}, {"Product", type text}, {"Value", Int64.Type}}
),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type", {"Category"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Product", each "Others", type text),
#"Added Custom1" = Table.AddColumn(
#"Added Custom",
"Value",
each
let
thiscat = [Category],
cattotal = List.Sum(
Table.SelectRows(
#"Changed Type",
each [Category] = thiscat and [Product] = "CategoryTotal"
)[Value]
),
prodtotal = List.Sum(
Table.SelectRows(
#"Changed Type",
each [Category] = thiscat and [Product] <> "CategoryTotal"
)[Value]
)
in
cattotal - prodtotal,
Int64.Type
),
#"Appended Query" = Table.Combine({#"Added Custom1", #"Changed Type"})
in
#"Appended Query"
Or you can use a Measure expression with this pattern.
Others Amt =
VAR totalamt =
CALCULATE ( SUM ( Others2[Value] ), Others2[Product] = "CategoryTotal" )
VAR productamt =
CALCULATE ( SUM ( Others2[Value] ), Others2[Product] <> "CategoryTotal" )
RETURN
totalamt - productamt
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Anonymous ,
Not certain what is your expected output based on your description. Perhaps you can consider sharing some sample data like @ mahoneypat mentioned, it looks like you have a dynamic table which would be updated sometimes.
By the way, the formula written by you could be modifed like this if it is your expected:
Re =
VAR totalA =
CALCULATE ( SUM ( 'data'[€] ), 'data'[brand] = "Others" )
VAR totalB =
CALCULATE ( SUM ( 'data'[€] ), 'data'[brand] <> "Others" )
RETURN
IF ( 'data'[brand] = "Others", A - B, B )
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I assumed your input structure and here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WclTSUQooyk8pTS4xBDKNlWJ1UASNgExTuKBzYklqen5RZUh+SWIOkG8IkXJCqDcGMk3QBU3ghjhhMwRoaSwA",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Category = _t, Product = _t, Value = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Category", type text}, {"Product", type text}, {"Value", Int64.Type}}
),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type", {"Category"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Product", each "Others", type text),
#"Added Custom1" = Table.AddColumn(
#"Added Custom",
"Value",
each
let
thiscat = [Category],
cattotal = List.Sum(
Table.SelectRows(
#"Changed Type",
each [Category] = thiscat and [Product] = "CategoryTotal"
)[Value]
),
prodtotal = List.Sum(
Table.SelectRows(
#"Changed Type",
each [Category] = thiscat and [Product] <> "CategoryTotal"
)[Value]
)
in
cattotal - prodtotal,
Int64.Type
),
#"Appended Query" = Table.Combine({#"Added Custom1", #"Changed Type"})
in
#"Appended Query"
Or you can use a Measure expression with this pattern.
Others Amt =
VAR totalamt =
CALCULATE ( SUM ( Others2[Value] ), Others2[Product] = "CategoryTotal" )
VAR productamt =
CALCULATE ( SUM ( Others2[Value] ), Others2[Product] <> "CategoryTotal" )
RETURN
totalamt - productamt
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Can you provide some sample data, so that more specific help can be provided? You can insert a table into your reply, or provide a link to a pbix, xlsx, csv. If someone doesn't respond during the day, I will provide a measure and a query solution this evening. Please @ mention me in your response if so.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Are you looking to add "Other" to your table in the query editor? DAX column? Or just create a measure? If a measure, you can just create two variables (one with logic to get just the total and the other with everything but the total) and subtract them.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Either would be great. Right now I am trying to create a measure. Any help would be great because I'm completely stuck here. If it's possible to do it in query that would be awesome.
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.