how to add two rows to make a DM

[singh_gagan30]

HI All,

I have sample data in rows:

CYCLE   YEAR   PERIOD  AMOUNT-TYPE   AMOUNT

201912  2019   12          ACTUAL               10

201912  2019   12          FORECAST           20

RESULTING ROW

CYCLE   YEAR   PERIOD  AMOUNT-TYPE   AMOUNT

201912  2019   12          ACTUAL               10

201912  2019   12          FORECAST           20

201912 2019    12         ACTUAL+FORECAST 30

TIA

[Mariusz]

Hi @singh_gagan30 

1. You can add a table like below to do it with DAX later you will be able to hide the original table.

Table 2 = 
UNION(
    'Table',
    GROUPBY(
        'Table',
        'Table'[CYCLE],
        'Table'[YEAR],
        'Table'[PERIOD],
        "AMOUNT-TYPE", MAXX( CURRENTGROUP(), "ACTUAL+FORECAST" ),
        "AMOUNT", SUMX( CURRENTGROUP(), 'Table'[AMOUNT] )
    ) 
)

2. You can do a similar operation in Power Query and load Transformed table.

3. The best way to handle your scenario would be by pivoting your data so the result is like below.

Please see the attached for reference

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[singh_gagan30]

Hi,

I was previously doing it by pivot only but I had some null values being encountered on my end.

CYCLE   YEAR    PERIOD   AMOUNT TYPE   AMOUNT

201911 2019     11          ACTUAL                5

201912 2019     12           ACTUAL              10

201912 2019     12          FORECAST           20

201913 2019     13          FORECAST           30

After pivoting 

CYCLE   YEAR  PERIOD  ACTUAL   FORECAST   SUM

201911 2019   11          5               null              null

201912 2019   12          10             20               30

201913 2019   13          null           30               null

After unpivoting it only increases the number of rows and that it is undesirable.

[Mariusz]

Hi @singh_gagan30 

Why not keep it as below, this way is a lot easier to compare the values in visuals.

CYCLE   YEAR  PERIOD  ACTUAL   FORECAST   SUM

201911 2019   11          5               null              null

201912 2019   12          10             20               30

201913 2019   13          null           30               null

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Ashish_Mathur]

Hi,

Drag the first 3 columns to your Table visual and write this measures

=SUM(Data[Amount])

Hope this helps.

[singh_gagan30]

I want to do it in dax only

[v-joesh-msft]

Hi @singh_gagan30 ,

You can also do what you want in the Power Query Editor. Copy the following M Query into the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDQ0UtIBM4AUmO3oHBLq6APiGSjF6uBQ5OYf5OrsGBwCFlaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CYCLE = _t, YEAR = _t, PERIOD = _t, #"AMOUNT-TYPE" = _t, AMOUNT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CYCLE", Int64.Type}, {"YEAR", Int64.Type}, {"PERIOD", Int64.Type}, {"AMOUNT-TYPE", type text}, {"AMOUNT", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CYCLE", "YEAR", "PERIOD"}, {{"AMOUNT-TYPE", each Text.Combine([#"AMOUNT-TYPE"],"+"), type text}, {"AMOUNT", each List.Sum([AMOUNT]), type number}})
in
    #"Grouped Rows"

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EenGn2PKQUlLvu0d0h...

Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[singh_gagan30]

COuld you please share the dax for this operation.

[v-joesh-msft]

Hi @singh_gagan30 ,

You can refer to the method of  @amitchandak , it is also reflected in my demo, you can take a look:

Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[singh_gagan30]

Hi,

Your solution is full justified as I have given an incomplete result requirement. I have updated it the result requirement. Please help me with that. I don't want another column but the calculation should happen in the rows itslef.

Regards

[amitchandak]

Create a new measure using concatenatex on AMOUNT-TYPE

https://docs.microsoft.com/en-us/dax/concatenatex-function-dax

AMOUNT-TYPE Concat = CONCATENATEX(table,AMOUNT-TYPE)

The amount you can aggregate as a sum. Use these measures in a matrix table (or graph ), you can get the desired output.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...


https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[az38]

@singh_gagan30 

try new calculated table

Table = SUMMARIZE(
'Table', [CYCLE], [YEAR], [PERIOD], "RESULTING ROW", sum([AMOUNT])
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[singh_gagan30]

I want to do it in the same table

[ToddChitt]

Try this DAX statement to SUMMARIZE the table:

My Summary Table = SUMMARIZE ( 'Table Name', 'Table Name'[CYCLE], 'Table Name'[Year], 'Table Name'[Period], "Total", SUM('Table Name'[Amount]).

Then add the [Amount Type] column manually as a static value: