Forum Discussion
Keep Filtered Value in New Table
Hi,
I have metered data for a few different sites (identified by MeterSerialNumber1)
And if I plot them as a line chart, It will look like this
If I click "Show as table", the table below is shown.
Which is a sum of kWh for all the filtered MeterSerialNumber1 grouped by the Date.
I am looking to transform the dataset I have on the first picture, to the one pointed by the blue line.
i.e. To create a new table. I would also like it to be dynamic as I may need to filter the MeterSerialNumber1.
My pbix file here.
Previous post : https://community.powerbi.com/t5/DAX-Commands-and-Tips/Keep-filtered-value-in-new-table/m-p/1013720#M13004
Thanks.
4 Replies
Hi Anonymous ,
Whn you refer to creating a new table what do you exactly mean?
You want to have a table visualization on your report?
You need to have a new table with the summarization of the data so you can do adittional calculations?
If you just create a table visualization with the values you have on the line chart you will get a table with the results equal to the line chart no need to create a new table just a new visualization, this will be them interacted with with the filters /slicers you need.
Can you explain a little bit better what you need to achieve.
Hi MFelix ,
Thanks for your reply.
Basically the background to this report is that I have half-hourly Smart Meter data for electricity consumption on our network. Its data format is as below:
DateTime Meter Serial Number kWh (Consumption) 1/1/2020 12AM ABC 0.5 1/1/2020 12AM DEF 0.5 So if say I have a residential estate (let's call this Residential Estate #1) with 10 houses, it could be that only 7/10 have metered data, thus the other 3 houses would need to be estimated. I calculate this by normalising the data for the 7 houses at each datetime and adding it on top of the metered data to have "Estimated total Consumption".
So I'm seeing if I could sum the total kWh at each Datetime for a collection of Meter Serial Numbers, at this case will be ABC and DEF, so I'm expecting:
DateTime kWh (Consumption) 1/1/2020 12AM 1 And I'm thinking I could just put another column for the calculated value as another column so I will have:
DateTime kWh (Consumption) kWh (calculated) 1/1/2020 12AM 1 0.4286 Then I would be able to make another column which would be (Consumption + Calculated) and graph this as a line chart something like the below. So the graph would represent actual metered data with calculated values for those houses that have no metered data.
The problem comes if I want to filter for residential estate #2, #3 ........ N. It wont be feasible to create multiple tables as there are >1000 unique entries ("residential estates").
Apologies if I still havent answered clearly enough, but do let me know and I can try again.
Thanks for your help and guidance.
Kind regards,
Eric Sng
Hi Anonymous.
You don't need to create a new table you can create measure to calculated those estimations based on your data.
If you can tell me how you are aking the normalization and calculation to get the 0.4286 of calculated, alonside with how you are getting the 7 houses from 10 you can have a measure to have the calculations made without the neeed of new tables.
Can you share some more insigths on your model and the calculations you are making?
Hi Anonymous ,
Sorry for that , but the calculated tables are computed during the database processing and then stored in the model. So In your scenario, we cannot make the calculated table dynamically.
We can use a table visual to show it grouped by date, table visual can change dynamically. If it does not contain any confidential information, could you please share the purpose why you want to create such a calculated table so that we may provide other workaround?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
