Reply
Frequent Visitor
Posts: 4
Registered: ‎12-18-2018
Accepted Solution

Dashboard of multiple column

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


Accepted Solutions
New Contributor
Posts: 450
Registered: ‎08-25-2016

Re: Dashboard of multiple column

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")

 

mup1.png

2) Remove the original 6 columns Date1 thru Val3

 

mup2.png

3) select the new merged columns and unpivot

 

mup3.png

4) split the new value column by delimeter "-"

 

mup4.png

5) remove the column "Attribute" and rename the value columns

 

mup5.png

 

Sorry for the confusion. Hope this helps

David

 

View solution in original post


All Replies
New Contributor
Posts: 450
Registered: ‎08-25-2016

Re: Dashboard of multiple column

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

Frequent Visitor
Posts: 4
Registered: ‎12-18-2018

Re: Dashboard of multiple column

Yes but how can I reformat it like this ?

New Contributor
Posts: 450
Registered: ‎08-25-2016

Re: Dashboard of multiple column

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

 

up1.png

 

up2.png

 

up3.png

Frequent Visitor
Posts: 4
Registered: ‎12-18-2018

Re: Dashboard of multiple column

Hi David, Thanks it works great but the line are duplicated with this solution. If I make a sum of your column Value.1, the value are duplicate by date. How can you eliminate the doublon ? Thanks, Tristan
New Contributor
Posts: 450
Registered: ‎08-25-2016

Re: Dashboard of multiple column

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")

 

mup1.png

2) Remove the original 6 columns Date1 thru Val3

 

mup2.png

3) select the new merged columns and unpivot

 

mup3.png

4) split the new value column by delimeter "-"

 

mup4.png

5) remove the column "Attribute" and rename the value columns

 

mup5.png

 

Sorry for the confusion. Hope this helps

David

 

Frequent Visitor
Posts: 4
Registered: ‎12-18-2018

Re: Dashboard of multiple column

Thanks David it works great Smiley Wink