Need to pivot a table where i sum the value of a column when there is a count of 1

[Anonymous]

Hi everyone,

Trying to find a solution. Need to pivot a table where i sum the value of a column when there is a count of 1.

Current Table looks like this

E.g.

Notification	Date	Impression	Blank Copy	Blank Image	Mispelling
ABC	09/09/2020	1400	0	1	0
XYZ	09/09/2020	600	1	1	0
FBA	09/09/2020	500	1	0	1
TOTAL		2500	2	2	1

The result table is sort of like a pivot of the summary where for each count of 1, you add the impression per each column.

If date slicer is 09/09/2020

Error Type	Impression
Blank Copy	1100
Blank Image	2000
Mispelling	500

Anyone have a solution? I am using direct query mode so i am limited to only DAX calculated columns and measures.

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

1. Enter data to create a "ColumnName" table and sort "ColumnName" column by "Order" column.

2. No relationship between your fact table and the "ColumnName" table.

3. Create a measure like so:

Measure =
SWITCH (
    SELECTEDVALUE ( ColumnName[ColumnName] ),
    "Blank Copy", CALCULATE ( SUM ( 'Table'[Impression] ), 'Table'[Blank Copy] = 1 ),
    "Blank Image", CALCULATE ( SUM ( 'Table'[Impression] ), 'Table'[Blank Image] = 1 ),
    "Mispelling", CALCULATE ( SUM ( 'Table'[Impression] ), 'Table'[Mispelling] = 1 )
)

4. Create a Table visual or a Matrix visual.

Best Regards,

Icey

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

[Fowmy]

@Anonymous 

You can do it in Power Query and have a table which can be used in your model to filter by Date and Error Type. Paste the below code in a blank Query and check the steps, easy 🙂

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMrDUByIjAyMDIMfQxABEgZlgOlYnWikiMgpdnZkBTAlCmZuTI7oyUwOESSA6NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Notification = _t, Date = _t, Impression = _t, #"Blank Copy" = _t, #"Blank Image" = _t, Mispelling = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Impression", Int64.Type}, {"Blank Copy", Int64.Type}, {"Blank Image", Int64.Type}, {"Mispelling", Int64.Type}, {"Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Notification", "Impression", "Date"}, "Attribute", "Value"),
    #"Inserted Multiplication" = Table.AddColumn(#"Unpivoted Other Columns", "Multiplication", each [Impression] * [Value], Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Multiplication", {"Date", "Attribute"}, {{"Impressions", each List.Sum([Multiplication]), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Attribute", "Error Type"}})
in
    #"Renamed Columns"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Hi @Fowmy,

Unfortunately I can't use M/ Power Query since I am using SAP Hana as a source and bringing the data through Direct Query instead of import so this method would not work 😞 I am only able to use calculate columns and measues.

[Fowmy]

@Anonymous 

The best solution that fits your need is to request the data source owner to have it arranged in the layout you need for your analysis.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn