Forum Discussion
DAX formule need help
Hi guys,
I am struggeling with the following problem. I have below DAX formule and Ithe value that I am getting in the row is correct, but I realy dont understand why I get another value by the total? So I need to see the value in the row (369....) in the left card visual instead of 182....
Thanks in advance!
_YTD Target_test =
VAR GMID = ([_KPI Lower Limit] + [_KPI Upper Limit]) / 2
VAR B = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),ENDOFQUARTER('Dim Kpi'[dat_end]),DAY)
VAR A = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),[Business_date (current)],DAY)
RETURN
GMID * (A/B)
Hi Anonymous and Greg_Deckler ,
I have finaly the solution. The issue was that there were no ratio between the table KPI and Date table. So I fixed this with the following formule.
_YTD_Target_Gauge = VAR KWARTAAL = SWITCH( TRUE(), [Business_date (current)] >= DATE(YEAR([Business_date (current)]),1,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),3,31), "Q1", [Business_date (current)] >= DATE(YEAR([Business_date (current)]),4,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),6,30), "Q2", [Business_date (current)] >= DATE(YEAR([Business_date (current)]),7,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),9,30), "Q3", [Business_date (current)] >= DATE(YEAR([Business_date (current)]),10,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),12,31), "Q4") VAR GMID = CALCULATE((SUM('Fact Target'[amt_lower_limit]) + SUM('Fact Target'[amt_upper_limit])) / 2,LEFT('Dim Kpi'[kpi_name],2) = KWARTAAL) VAR EINDKWARTAAL = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),ENDOFQUARTER('Dim Kpi'[dat_end]),DAY) VAR YTDDAGEN = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),[Business_date (current)],DAY) RETURN GMID*(YTDDAGEN/EINDKWARTAAL)And then another formule which Rico gave me:
TGT_Gauge = SUMX('Dim Kpi',[_YTD_Target_Gauge])Thank you guys to thinking about this issue!
6 Replies
Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907Hi Greg_Deckler ,
Thanks for quick response! Unfortnetuly my situation is a little bit complex which I still cannot get the value I want.
In mine situatie I use 3 differents tables see formule below. I really dont how to fix this.
_YTD Target_test1 = VAR GMID = ([_KPI Lower Limit] + [_KPI Upper Limit]) / 2 //From FACT_Target table VAR EINDKWARTAAL = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),ENDOFQUARTER('Dim Kpi'[dat_end]),DAY) //BusinessDate is from Snapshot table and Dim KPI is another table VAR YTDDAGEN = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),[Business_date (current)],DAY) RETURN GMID * YTDDAGEN/EINDKWARTAALAnonymous Going to need more information then like sample data, relationships, etc.
Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi Anonymous
Total in table visual will show you summarize result. Your measure looks like as below.
_YTD Target_test = VAR GMID = ([_KPI Lower Limit] + [_KPI Upper Limit]) / 2 VAR B = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),ENDOFQUARTER('Dim Kpi'[dat_end]),DAY) VAR A = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),[Business_date (current)],DAY) RETURN GMID * (A/B)Your measure is based on many different parts.
Such as [KPI lower limit], [_KPI Upper Limit], B and A. All of them will show summarize result in Total.
Here I create a sample to explain it more to you.
My Sample.
My Measure has same logic like yours.
Measure = VAR _GMID = DIVIDE(SUM('Table'[KPI lower limit])+SUM('Table'[KPI upper limit]),2) VAR _A = SUM('Table'[Day1]) VAR _B = SUM('Table'[Day2]) RETURN _GMID*(_A/_B)Result:
We see Q2 has result 4, this result is based on (3+1)/2 *(2/1)
Total is based on summarize result, (8+8)/2 *(2/6) = 2.67
Here I suggest you to create a new measure based on [_YTD Target_test] measure by Sumx function.
New Measure = SUMX('Table',[Measure])Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous,
I tried your solution, but unfortunately I have the same result. So mabye below information give you more explanation about my situation.
So in the first table I want to see Q2 because in the filter I selected 31-5-2021.In the second table you see the PK of the 3 tables, KPI, Target and Date(snapshot).
So what I need is when I select a business date from the filter that my measure only select the right quarter and the value of my measure for that quarter.
I tried your last measure SUMX(table,...) I tried all the 3 tables no one of them works. In your case you have only 1 table.
Please help Thank you!
Hi Anonymous and Greg_Deckler ,
I have finaly the solution. The issue was that there were no ratio between the table KPI and Date table. So I fixed this with the following formule.
_YTD_Target_Gauge = VAR KWARTAAL = SWITCH( TRUE(), [Business_date (current)] >= DATE(YEAR([Business_date (current)]),1,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),3,31), "Q1", [Business_date (current)] >= DATE(YEAR([Business_date (current)]),4,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),6,30), "Q2", [Business_date (current)] >= DATE(YEAR([Business_date (current)]),7,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),9,30), "Q3", [Business_date (current)] >= DATE(YEAR([Business_date (current)]),10,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),12,31), "Q4") VAR GMID = CALCULATE((SUM('Fact Target'[amt_lower_limit]) + SUM('Fact Target'[amt_upper_limit])) / 2,LEFT('Dim Kpi'[kpi_name],2) = KWARTAAL) VAR EINDKWARTAAL = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),ENDOFQUARTER('Dim Kpi'[dat_end]),DAY) VAR YTDDAGEN = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),[Business_date (current)],DAY) RETURN GMID*(YTDDAGEN/EINDKWARTAAL)And then another formule which Rico gave me:
TGT_Gauge = SUMX('Dim Kpi',[_YTD_Target_Gauge])Thank you guys to thinking about this issue!
