Delighted to join the forum. May I ask for some assistance, please?
My scenario represents a “green energy” initiative. I have a major fact table “AllEnergy” that contains details of gas and electricity budget, spend and use month by month for 2018 and 2019. I have a scenario that allows me to distinguish between each of them, Electricity and Gas. Relationships are between Date in Calendar and Date in AllEnergy. You can see my measures and that all seems to work.
There are two other tables, GasGHG and ElecGHG each of which contains annual conversion factors allowing usage (in kWh) to be converted into whatever. In reality, these tables are identical and could be merged with a new column simply to distinguish gas from electricity. Should I do that?
I can link each of these with Calendar by Date and Page 2 shows that that relationship works.
What I need to is to create a measure that will multiply US (Gas example here) (US = CALCULATE(sum(AllFuel_2[Value]), filter(AllFuel_2,AllFuel_2[Scenario]=“Use”)))
by a category factor, say, “Tonnes KG N20” to produce the monthly, annual and PY “Total Tonnes N20”. I will, in fact,SERC DD Alternative for Enterprise DNA.pbix (358.1 KB) need to multiply US by each category in the GasGHG table.
Could you take a look and offer some advice, please?. My major issue is to make the link between US and the GHGGas Table. I’m thinking of inheritance via Calendar (?). There is no natural relationship.
PBIX is attached
Hi @DJBrennan, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
-
Use the forum search to discover if your query has been asked before by another member.
-
When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
-
Use the proper category that best describes your topic
-
Provide as much context to a question as possible.
-
Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.
Regarding appending two tables, yes, please do so, that will be more efficient way. Regarding multiplying US measure with each category in the GasGHG table you could use something like this.
Measure =
VAR SelectedUOM =
SELECTEDVALUE ( GHGGas[UOM] )
VAR Result =
[US]
* SUMX (
FILTER (
SUMMARIZE ( GHGGas, GHGGas[UOM], GHGGas[GHG Conversion Factor] ),
GHGGas[UOM] = SelectedUOM
),
GHGGas[GHG Conversion Factor]
)
RETURN
Result
2 Likes
Works perfectly. Many thanks. I now understand how you built up the logic and, hopefully, will be able to repeat in future.
Best wishes,
Denis
