Dashboard of multiple column

[tristang]

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

[dedelman_clng]

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]

Yes but how can I reformat it like this ?

[dedelman_clng]

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

[tristang]

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 · Accepted 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

[tristang]

Thanks David it works great 😉