cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tristang Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
dedelman_clng New Contributor
New Contributor

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

6 REPLIES 6
dedelman_clng New Contributor
New Contributor

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

tristang Frequent Visitor
Frequent Visitor

Re: Dashboard of multiple column

Yes but how can I reformat it like this ?

dedelman_clng New Contributor
New Contributor

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

tristang Frequent Visitor
Frequent Visitor

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
dedelman_clng New Contributor
New Contributor

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

tristang Frequent Visitor
Frequent Visitor

Re: Dashboard of multiple column

Thanks David it works great Smiley Wink

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 126 members 1,614 guests
Please welcome our newest community members: