cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Calculating "Others" from category total

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.

1 ACCEPTED SOLUTION
mahoneypat
Microsoft
Microsoft

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
v-yingjl
Community Support
Community Support

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
Microsoft
Microsoft

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mahoneypat
Microsoft
Microsoft

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mahoneypat
Microsoft
Microsoft

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors