Forum Discussion
Calculating a % between two numbers in two queries
I don''t recommend joining your fact tables to each other (i.e. joining Query1 to Query2), a star schema should be used. I would also still recommend creating the individual explicit measures, but if you don't do that you could do Percentage = DIVIDE(SUM('Query1'[Items]),SUM('Query2'[Remakes]))
Hello! You will have to create a star schema model (i.e. fact and dimension). I have a link to a blog I wrote about it here: http://powerbiwithme.com/2023/07/30/the-star-schema-edition/
For your model, you should have a customer dim table and a date dim table. Once you have those, join them to each of your two fact tables (query 1 and query 2).
I also like to have a measures table. To create one, click Enter Data in Power BI and name the table Key Measures (or whatever you want). Once the new table is there, create a measure for Items; Items = SUM('Query1'[Items]). Next, create a measure for Remakes; Remakes = SUM('Query2'[Remakes]). Next, create your percentage measure; Percentage = DIVIDE(Items,Remakes). Then, format as percentage.
Any visuals you make that need to use customer name - grab the field from the customer dim table instead of query1 or query2 (in fact, I would hide customer in both of those tables). Ditto on date lements - these will now come from the date table and you can hide the date fields in query1 and query2. Here is a link to a blog I wrote about date tables and it has a link to the reference date table I love from SQLBI: http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/
Ill try and understand that .
Can i not do a measure
Percentage = DIVIDE('Query1' Items - Query2 Remake Items ?How would i write that ?I don''t recommend joining your fact tables to each other (i.e. joining Query1 to Query2), a star schema should be used. I would also still recommend creating the individual explicit measures, but if you don't do that you could do Percentage = DIVIDE(SUM('Query1'[Items]),SUM('Query2'[Remakes]))
