Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |