Calculating a % between two numbers in two queries

[Benc7777]

I hope someone can help.

I have tried quick measure and it crashes everytime.

So I have two queries runniing from SQL, linked a relationship between "Customer Name" single slicer searches both, lovely.

All I want is a seperate box in the percentage of Sum of items / Sum of Remakes - I honestly cannot figure out how. What am I dong wrong its driving me crazy. I have never used DAX so i cant understand it.

Please can anyone help ??

[Benc7777]

@audreygerredYou have saved me some grey hairs, I will read through your posts tonight.

A great thank you for taking the time to help , really appriciated !

[audreygerred]

Happy to help! Good luck on your Power BI journey!!!

[audreygerred]

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/

[Benc7777]

Ill try and understand that .

Can i not do a measure

Percentage = DIVIDE('Query1' Items - Query2 Remake Items ?

How would i write that ?

[audreygerred · Accepted solution]

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]))

DIVIDE function (DAX) - DAX | Microsoft Learn