Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Peter_Price
Frequent Visitor

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

1 ACCEPTED SOLUTION

@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 )

CTabl.png


Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
Peter_Price
Frequent Visitor

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

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"

 

 


Regards
Zubair

Please try my custom visuals

@Peter_Price

 

File attached as well

 

You can follows the steps from Query Editor

 

PQSolution.png


Regards
Zubair

Please try my custom visuals

@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 )

CTabl.png


Regards
Zubair

Please try my custom visuals
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.