How to compare values (calculate diferences) from the same colum as were two colums

[Lucian]

Hello,

I have a report requirement, for which I cannot find a way to achieve it, if possible, in Power BI Desktop.

The raw data is in Excel and after combining multiple source files it look like this:

Because I don't have the option to upload files I will put the raw data as CSV:

Year,Project,CostCenter,Cost
FY_2010,A,C1,1000
FY_2010,A,C1,500
FY_2010,A,C2,500
FY_2010,B,C1,1000
FY_2010,C,C2,1000
FY_2011,A,C1,1500
FY_2011,A,C1,500
FY_2011,B,C1,1500
FY_2011,C,C1,1000
FY_2012,A,C1,2000
FY_2012,A,C1,1000
FY_2012,B,C1,2000
FY_2012,C,C2,1500

The Cost colum is the only numeric colum that will imply calculations. The Year colum even is related to a certain period of time, is just a "text label".

The report should allow the user select only two YEAR values, and I should identify which is FIRST/SECOND selection because after selection I have to do some calculations between them. For example make a simple diference SECOND-FIRST.

The final table should look like this:

The hard part for me is to have in this final table "dynamic colum names" because I have to see which are the selected Years.

I have partially achieved this with two calculated measures based on two slicers and renaming the table columns manually after user selection, but it has to be dynamic. I cannot use matrix visual because of the measures.

Is there a way to have a table with dynamic column names? - if I can rename column manually is there a way to dynamically create the name of the column based on the slicer selection?

Is there another way to achieve this goal? Because of the fixed format for the final table, any suggestion is welcomed (even reorganizing data).

Kind Regards,

Lucian

[sturlaws · Accepted solution]

Hi @Lucian,

Power BI does not support dynamic naming of measures. There are a couple of workarounds:
-You can use the R-visual or Python-visual.

-You can create measures which returns the names you want for the measures and show them in card visuals. Then set the table header color to white, and overlay the table headers with the cards

cheers,

Sturla

[Lucian]

Hi @sturlaws ,

And thank you for your time. Unfortunately I don't know too much about R-visuals or Python-visuals, but I'm a "fast learner" and if you could help me with the code to create the reduced table model from above picture, I might find a way to reproduce it for my real table. 

Until then I will use the card visuals, but in this case I will loose the sorting option and "focus mode" for this visual.

Meanwhile I post an ideea, and if voted, maybe we all benefit from it: Improved table visual - dynamic column names  

[sturlaws]

@Lucian,

yes, you are quite right, the cards solution is a work-around with limited functionality.

R and python will be better, but they kind of don't look like power bi. I occasionall dabble in R, and know enough about it to know that I should not advise others on how to do it. I can only give you a pointer: https://docs.microsoft.com/en-us/power-bi/desktop-r-visuals

Perhaps you will find answers elsewhere in the forum