Show quarter in column

[Anonymous]

I want to show value of every qtr in column for every name. instead of matrix it should be in table. and name should appear in every quarter. 

PS: i can't change my table into pivot table. 

Ex: 

INPUT TABLE
ID	name	QTR	Amt
123	a	Q1	50
123	a	Q2	10
456	c	Q3	50
456	c	Q4	50
456	c	Q1	50
456	c	Q2	50
1234345	f	Q3	50
1234345	f	Q4	50
1234345	f	Q1	50
1234345	f	Q2	50

OUTPUT TABLE
ID	Name	Q1	Q2	Q3	Q4	Total
456	c	50	50	50	50	200
1234345	f	50	50	50	50	200

[Ashish_Mathur]

Hi,

Just in case you wish to do this in the Query Editor, this M code does the job

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"name", type text}, {"QTR", type text}, {"Amt", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[QTR]), "QTR", "Amt"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Q1", "Q2", "Q3", "Q4"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Total", each [Q1]+[Q2]+[Q3]+[Q4])
in
    #"Added Custom"

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You can create a calculated table like this:

Table = 
VAR tab =
    SUMMARIZE (
        'Input Table',
        'Input Table'[ID],
        'Input Table'[Name],
        "Q1", CALCULATE ( SUM ( 'Input Table'[Amt] ), 'Input Table'[QTR] = "Q1" ),
        "Q2", CALCULATE ( SUM ( 'Input Table'[Amt] ), 'Input Table'[QTR] = "Q2" ),
        "Q3", CALCULATE ( SUM ( 'Input Table'[Amt] ), 'Input Table'[QTR] = "Q3" ),
        "Q4", CALCULATE ( SUM ( 'Input Table'[Amt] ), 'Input Table'[QTR] = "Q4" )
    )
VAR newtab =
    SUMMARIZE (
        tab,
        'Input Table'[ID],
        'Input Table'[Name],
        [Q1],
        [Q2],
        [Q3],
        [Q4],
        "Total",
        VAR _rows = { [Q1], [Q2], [Q3], [Q4] }
        VAR _sum =
            SUMX ( FILTER ( _rows, [Value] <> BLANK () ), [Value] )
        RETURN
            _sum
    )
RETURN
    newtab

The table will like this:

You can put these fields in the table visual:

Attached my sample file that hopes to help you, please check and try it: Show quarter in column.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , As Hybrid table is not supported, you have tried a solution suggested by @Greg_Deckler . I have logged and idea for that, Vote for that. Or As of now, the way out is a column for each qtr.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/39773011-hybrid-table

[Greg_Deckler]

@Anonymous - Why can't you use a Matrix visualization? That seems to be the right choice for what you are going for. You might find this useful: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/td-p/963588