New Table for another one with Count

[larabraghetti]

Hi all,

in PowerBI, I have a table like this one:

And I need to create a new table like this one:

I need this 2nd table in order to built a new cascade graph.

Someone could help me?
Thank you very much in advance!
Ciao!
Lara

[CheenuSing · Accepted solution]

@larabraghetti

Please try the following

1. After the intial load of the table, right click on the table and select Edit Query

2. This will take you to QueryEditor

3. Go to the transform tab

4. Select the three columns of the table Opened atStartDate,OpenedThisMonth, ClosedThisMonth

5. Choose the icon a2 UnpivotColumn from the AnyColumn area

    

6. The Output of your intial table will be like

7. Click on File --> Close and Apply

8. Now create the newTable as 

    NewTable = Summarize( OpenClosed,[Attribute],"Counts",Calculate( Countrows(OpenClosed),

                                       OpenClosed[Value] ="Yes" || OpenClosed[Value] ="Opened" )   )

9. When you plot the columns from the NewTable in a Table Report Visual, the output will be as you desired.

  

If this solves your issue please accept this as a solution and also give KUDOS.

Cheers

CheenuSing

[larabraghetti]

@CheenuSing Thank you!

The problem is that the column used in my first table are Calculated Column.
There is no possibility to have these columns in the queryeditor. (only if I add the columns from there I think...)

[CheenuSing]

See if you can use the concept to derive the final output from the base source table instead of from intermediate table as posted by you.

Cheers

CheenuSing

[CheenuSing]

@larabraghetti

See if you can use the concept to derive the final output from the base source table instead of from intermediate table as posted by you.

Cheers

CheenuSing