Calculating "Others" from category total

[Anonymous]

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.

[v-yingjl]

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.

[mahoneypat · Accepted 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

[mahoneypat]

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

[mahoneypat]

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

[Anonymous]

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.