cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nataliengarces
Helper IV
Helper IV

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

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.

NotificationDateImpressionBlank CopyBlank ImageMispelling
ABC09/09/20201400010
XYZ09/09/2020600110
FBA09/09/2020500101
TOTAL 2500221

 

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 TypeImpression
Blank Copy1100
Blank Image2000
Mispelling500

 

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

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @nataliengarces ,

 

Try this:

 

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

enterdata.PNG

enter.PNGsort.jpg

 

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.

m.PNG

 

 

Best Regards,

Icey

 

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

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

Hi @nataliengarces ,

 

Try this:

 

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

enterdata.PNG

enter.PNGsort.jpg

 

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.

m.PNG

 

 

Best Regards,

Icey

 

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

View solution in original post

Fowmy
Super User IV
Super User IV

@nataliengarces 

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"

 

Fowmy_0-1599671021316.png

 

________________________

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

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.

@nataliengarces 

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.