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.
Hello,
I have 6 columns :
Business Unit ; Date #1 ; Cost #1 ;Date #2 ; Cost #2 ; Date #3 ; Cost #3
Value ; Value ; Value ; Value ; Value ; Value ; Value
Value ; Value ; Value ; Value ; Value ; Value ; Value
I would like to have the total Cost (#1 + #2 + #3) per Business Unit and per Month in a Dashboard.
I have checked in power BI without success , please let me know if there is any solution to this .
Thanks in advance
Tristan
Solved! Go to Solution.
You are correct. My mistake.
Here another way of doing it, but it seems convoluted. I'm not a Power Query expert so there may be an easier way, but others would have to chime in.
1) Create 3 merged columns, one for Date1-Val1, one for Date2-Val2, one for Date3-Val3 (use Add Column -> "Column from Examples")
2) Remove the original 6 columns Date1 thru Val3
3) select the new merged columns and unpivot
4) split the new value column by delimeter "-"
5) remove the column "Attribute" and rename the value columns
Sorry for the confusion. Hope this helps
David
If you reformat your data to look like
Business Unit Date Type Date Value Value
A 1 Date 1 $$
A 2 Date 2 $$
A 3 Date 3 $$
B 1 Date 1 $$
etc
You can sum on value and slice on Business Unit, Date, etc much more easily.
Hope this helps
David
Yes but how can I reformat it like this ?
In query editor, select the 3 date columns, then click Transform tab -> Unpivot columns
Then select the 3 value columns, and click Transform -> Unpivot columns
Remove the columns Attribute and Attribute.1 and rename the Value and Value.1 columns
You are correct. My mistake.
Here another way of doing it, but it seems convoluted. I'm not a Power Query expert so there may be an easier way, but others would have to chime in.
1) Create 3 merged columns, one for Date1-Val1, one for Date2-Val2, one for Date3-Val3 (use Add Column -> "Column from Examples")
2) Remove the original 6 columns Date1 thru Val3
3) select the new merged columns and unpivot
4) split the new value column by delimeter "-"
5) remove the column "Attribute" and rename the value columns
Sorry for the confusion. Hope this helps
David
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 |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |