Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I want to create a relation between 2 tables to create a ratio : on the numerator an amount from a table and on the denominator an amount on the other table.
The problem is I have a lot of values which are on the same date on each table. What I want is to make an histogram chart with the numerator and the denominateur with the same dates.
Is it clear?
Solved! Go to Solution.
You can create a measure with following DAX formula without creating relationship between these two tables.
Measure = VAR TempDate = CALCULATE ( MAX ( Table1[Date] ) ) VAR TempExpectedSales = CALCULATE ( SUM ( Table2[Expected Sales] ), FILTER ( ALL ( Table2 ), Table2[Date] = TempDate ) ) RETURN CALCULATE ( SUM ( Table1[Actual Sales] ) ) / TempExpectedSales
Best Regards,
Herbert
Hello,
Firstly, thank you for your answer.
The problem is I can't sum the amount of sales because I need to put filter on some colomns to have the right amount to put on the numerator, same for the denominator.
If I sum my amount per date, I loose every filters so I can't create the ratio.
Is there another solution except group by?
Thank you very much,
Julien
You can create a measure with following DAX formula without creating relationship between these two tables.
Measure = VAR TempDate = CALCULATE ( MAX ( Table1[Date] ) ) VAR TempExpectedSales = CALCULATE ( SUM ( Table2[Expected Sales] ), FILTER ( ALL ( Table2 ), Table2[Date] = TempDate ) ) RETURN CALCULATE ( SUM ( Table1[Actual Sales] ) ) / TempExpectedSales
Best Regards,
Herbert
It works on my file, thanks a lot.
I have a last question, here is my formula :
RETURN
(CALCULATE(CALCULATE(SUM('Table1'[Actual Sales]);OR('Table1[Filter]=1;'Table1'[Filter1]=2));'Table1'[Filter2]="00164"))/TempExpectedSales
But I want Filter2 (in red) not to be fixed. Can I use a code to change this filter from the rapport? It would be perfect if I could use a segment to change the filter of the measure directly to make the changes dynamic.
I don't know if I'm clear, again thanks a lot for your solution.
I'd like to give a simple sample as below (hope I understand your scenario exactly). We have two tables like below.
We can first summarize both of tables using the group function in Query Editor.
Now we can create relationship between these two tables.
To get the ratio, we just need to create a calculated column with following formula.
ratio_Column = Table1[Actual Sales] / RELATED( Table2[Expected Sales] )
Best Regards,
Herbert
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |