Making custom table for the visual

[AgencyPowerBi]

Hi,

I have a need for a visual that would show:

Full year 2018

Current year 2019

Current year 2020

YOY% 2019

YOY% 2020

Now, I made calculaton group with these calculations (FY, YTD, YOY%)

I need to make a chart in which I would leave only specific colums as above.

This can be done in excel through set definition on a pivot.

Can something similar be done in Power BI desktop?

Thanks!

[v-cazheng-msft]

Hi, @AgencyPowerBi 

You can create a Calculated table with these data you want to display and create visual with this table.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AgencyPowerBi]

Can you please help?! can you give me an example with Time Intelligence columns?

[v-cazheng-msft]

Hi, @AgencyPowerBi 

You can refer Basics of Time Intelligence in DAX for Power BI; Year to Date, Quarter to Date, Month to Date, Custom Year-Over-Year Calculation in DAX and DatesInPeriod vs DatesBetween; DAX Time Intelligence for Power BI.

Best Regards,

Caiyun Zheng

[AgencyPowerBi]

Hi,

thanks, but I can calculate the measures. That is not the problem.

My problem is making the table that I can visualise in the chart.

There are two ways: make the measures and put measures in the chart. That way I end up with three values for each year (FY, YTD and YOY%). I want to hide specific year measures from the chart.

Is there a visual that allows me to hide specific data (FY 2019, FY 2020, YTD 2018, for instance).

By making calculated table, Bar chart doesn't allow me to put calculated column into the chart.

So these two are my primary problems.

In excel, I would pivot the data, copy pivot, delete colums I dont need and make the visual. What is method for doing this in Power BI?

Than you!

[v-cazheng-msft · Accepted solution]

Hi, @AgencyPowerBi 

According to your description, I think you can try the two methods. Can you provide some sample data removing sensitive data if it doesn't work?

Method 1

Calculate the result of ‘Full Year 2018’, ‘YTD 2019’ ‘YTD 2020’ and ‘YOY%’ in original table, then use thee values to create a new table. Use the new table to create visual.

Method 2

Do calculation in original table, add filters to the dax formula and just do calculation for these specific years.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AgencyPowerBi]

Ok, I'll play around and see if it helps.

Tx

[AgencyPowerBi]

I need table like this. So I need hiding 2018 YTD, 2018 YOY, FY for 2019 and 2020, etc..

Im making bar chart, so I need as little as bars as possible.

I can do this in excel, by hiding pivot elements and making a set.

How do I do it here?

[amitchandak]

@AgencyPowerBi , At the end of this video. A measure slicer is been used. Check if that can help

https://www.youtube.com/watch?v=vlnx7QUVYME