Forum Discussion

Thejeswar's avatar
Thejeswar
Super User
8 years ago
Solved

Filter with calculated measures

Hello guys, I have been trying to create a calculated measure which should be filtered based on another measure. Here's my scenario   1. I want to create Total YTD of a column which I am doing by ...
Need Help
  • Floriankx's avatar
    Floriankx
    8 years ago

    Hello,

     

    What probably would work perfectly is a separate DateTable with contiguous Dates. Then you could add the DateTable columns to your PivotTable and use it for TOTALYTD.

     

    After trying a little bit this formula gave me the correct result as well:

    YTD Revenue:=VAR LatestDate=MAX(FactTable[Datekey])
RETURN
CALCULATE(SUM([column]);
     CALCULATETABLE(
      FILTER(FactTable;FactTable[Datekey]<=LatestDate&&YEAR([Datekey])=YEAR(LatestDate));
      ALL(FactTable)
     )
    )

     

Floriankx's avatar
Floriankx
Solution Sage
8 years ago

Hello,

 

What probably would work perfectly is a separate DateTable with contiguous Dates. Then you could add the DateTable columns to your PivotTable and use it for TOTALYTD.

 

After trying a little bit this formula gave me the correct result as well:

YTD Revenue:=VAR LatestDate=MAX(FactTable[Datekey])
RETURN
CALCULATE(SUM([column]);
 CALCULATETABLE(
  FILTER(FactTable;FactTable[Datekey]<=LatestDate&&YEAR([Datekey])=YEAR(LatestDate));
  ALL(FactTable)
 )
)

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi, I have a similar question with text filter and I know it's pretty stupid but I am not able to figure out a simple thing to do in DAX.

In the image above, I want to calculate Weighted Sales filtered on Types

Current formula for Weighted Sales = Sales/ Sum(Sales)

I am trying to do Weighted Sales = Sales/ [Sum(Sales), filtered by (Type)] - I want to see the weighted sales by types.

Whenever I am using DAX, I need to use Divide function and for numerators and denominators in it, I have either use SUM() or similar funcitons to perform calculation, I cannot do simple Column1/Column2.

Can anyone please help me in this?