Forum Discussion

Peter_Price's avatar
Peter_Price
Frequent Visitor
7 years ago
Solved

Power Query - how we create a measure based on a dataset while union it back to the original dataset

Hi Folks,

 

Wondering how we create a measure based on a dataset <Not only specific to date column> while union it back to the original dataset ?

 

May be it make it easier to view the problem visually, so i have created the picture below.

 

 

 

Link: Link

 

P.S. Somehow i have to do it in this way

 

thanks

Need Help
Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Peter_Price

     

    And you can also use a DAX Calculated Table as well.

    Assuming your Table Name is Table1.

    From Modelling Tab >> New Table

    File attached as well

     

    Calculated Table =
VAR Rows_I_need =
    GENERATE (
        SELECTCOLUMNS ( VALUES ( Table1[Category] ), "Mycategory", [Category] ),
        CALCULATETABLE ( TOPN ( 1, Table1, [Date], DESC ) )
    )
VAR Add_Volumn_Difference =
    ADDCOLUMNS (
        Rows_I_need,
        "Difference", [Volume]
            - CALCULATE (
                SUM ( Table1[Volume] ),
                TOPN (
                    1,
                    FILTER (
                        Table1,
                        Table1[Category] = [Mycategory]
                            && Table1[Date] < EARLIER ( [Date] )
                    ),
                    [Date], DESC
                )
            )
    )
VAR ComparisonTable =
    SELECTCOLUMNS (
        Add_Volumn_Difference,
        "Date", [Date],
        "Category", [Category],
        "Calc Type", "Comparison",
        "Volumn", [Difference]
    )
RETURN
    UNION ( Table1, ComparisonTable )

6 Replies

  • Peter_Price's avatar
    Peter_Price
    Frequent Visitor
    7 years ago

    Hi Folks,

     

    Wondering how we create a measure based on a dataset while union it back to the original dataset ?

     

    May be it make it easier to view the problem visually, so i have created the picture below.

     

    Link: Link

     

    thanks

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      7 years ago

      HI Peter_Price

       

      Try this

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9UrM0zW0UNJRcgRiz7yUzLLMlNLEHCDHUClWB1WJE7oSIwwlzuhKjCFKjPBYZIKhBMMiUwwlGBaZKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Category = _t, #"Calc Type" = _t, Volume = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Category", type text}, {"Calc Type", type text}, {"Volume", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"Date", each List.Max([Date]), type date}, {"All Rows", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Max Rows", each let mydate=[Date] in 
Table.SelectRows([All Rows],each [Date]= mydate)),
    #"Expanded Max Rows" = Table.ExpandTableColumn(#"Added Custom1", "Max Rows", {"Volume"}, {"Max Rows.Volume"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Max Rows", "Day Defore", each let mydate=[Date] in 
Table.SelectRows([All Rows],each [Date]= Date.AddDays(mydate,-1))),
    #"Expanded Day Defore" = Table.ExpandTableColumn(#"Added Custom", "Day Defore", {"Volume"}, {"Day Defore.Volume"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Day Defore", "Volume", each [Max Rows.Volume]-[Day Defore.Volume]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"All Rows", "Max Rows.Volume", "Day Defore.Volume"}),
    #"Added Custom3" = Table.AddColumn(#"Removed Columns1", "Calc Type", each "Comparison"),
    #"Appended Query" = Table.Combine({#"Changed Type", #"Added Custom3"})
in
    #"Appended Query"

       

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    If the entries in the Date column are continuous, then try this

     

    1. Create a Calendar Table with a relationship from the Date column of your source data Table to the Date column of your Calendar Table
    2. Build a simple Table visual by dragging Date (from the Calendar Table) and Category columns
    3. Write this measure = SUM(Data[Volume])-CALCULATE(SUM(Data[Volume]),PREVIOUSDAY(Calendar[Date]))

    Hope this helps.

    • Peter_Price's avatar
      Peter_Price
      Frequent Visitor
      7 years ago

      Hi Ashish,

       

      thanks for your reply and it is great.Sry that i didn't make myself clear.  Somehow i want to do

       

      Create a measure based on existing dataset , it may involve sum(Cat B) - Sum(Cat C)  < Not only specific to date column>

      then union that measure back to origninal dataset 

       

      Thx