I’m building a CO2 emissions dashboard and am having a problem converting “usage” to “emissions in kgCO2”. I’ve built a page to show my calculations. I have a Year slicer, a table showing the contents of “Conversion factors” table, two tables showing (a) “usage” (in miles per vehicle type) and (b) “Emissions in kgCO2”. I have screenshot this page (attached) to try to make it easy for you guys to visualize.
Any help gratefully appreciated.
Hi @DJBrennan. Is your [Year] table related to your [Conversion Factors] table (i.e., Year[Year] --> ‘Conversion Factors’[Year]? If not, perhaps you could upload your work-in-progress PBIX and a mockup of the outcome you’re looking for…
SECR Version 5 All sitesf3d0e35c-df86-438e-86b7-95dcfc2a8990.pbix (2.4 MB)
Here’s the PBIX (I think the screenshot serves as a mockup). You’re right that there is no relationship but it appears that if I try to create one it’s “many to many cardinality” so to be avoided.
Because your “year” in the CGFactors table is actually a date field, you can link it to the date table via the date field. Then on the table view in your report, you can use the Year value from the date table instead of the value from the CGFactors table.
You will need to expand your date table to deal with 2020.
Let me try this. 2020, by the way is a red herring, as there’s no data.
Hi @DJBrennan. As Heather said, the data type mismatch between the [Year] your dates and conversion factors table is an issue. As with most things in Power BI, there are many ways to accomplish the same thing; here’s an example using a calculated column in the CGFactors table
YearText = FORMAT( YEAR( CGFactors[Year] ), "#" )
and a multiple-column LOOKUPVALUE to retrieve the appropriate conversion factor
KGCO2 Fleet Cars Diesel CY 2 = VAR _SelectedYear = SELECTEDVALUE( Dates[Year] ) -- this is text format in your [Dates] table VAR _CurrentNature = "Fleet Cars" VAR _CurrentLevel3 = "Cars average size Diesel" VAR _DieselMiles = [Fleet Car Miles Diesel] VAR _ConversionFactor = LOOKUPVALUE( CGFactors[Value], CGFactors[YearText], _SelectedYear, CGFactors[Nature], _CurrentNature, CGFactors[Level 3], _CurrentLevel3 ) VAR _Result = _DieselMiles * _ConversionFactor RETURN _Result
Is something like this what you’re looking for?
Hope this helps.
eDNA Forum - Conversion Factors.pbix (2.4 MB)
Hi @DJBrennan, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!