Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 t...
General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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!



     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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!