Forum Discussion

marapdq1993's avatar
marapdq1993
Frequent Visitor
5 years ago
Solved

DAX: Apply different formula based on the selected component

Hello,

 

I would need advice on a problem I have in PowerBI. I think it is feasible but can't seem to figure it out.

 

My actual data looks something like below. I have a column M/N (for which only Component CBD will have a budget value but actuals all have numbers) else it will just be null.  Kindly note that budget will have data until end of year (Dec 2021). 

 

 

 

What I wanted to see is something like below in PowerBI.

 

A. If CBD is selected, it will show the filtered values for both actual and budget, with the budget values for future months still showing.

 

 

 

B. If all components or other components are selected and either M or N are selected, the actual values will still filter but budget will show the total for the selected component and not blank

 

I was initially thinking of doing a calculated measure for the Budget -> IF(SELECTEDVALUE([Component]) = "CBD", CALCULATE(SUM([Budget])), ...)?

 

Hoping to hear any advice/suggestions on this. Thanks so much!

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi marapdq1993 ,

     

    You need to create a separate table with components.

     

    Create two measures

    FilteredAcutal = CALCULATE(SUM('Table'[Actual]),FILTER('Table',[Component] IN ALLSELECTED('Table (2)'[Component])))
    FilteredBudget = IF(SELECTEDVALUE('Table (2)'[Component])="CBD",CALCULATE(SUM('Table'[Budget]),'Table'[Component]="CBD"),SUM('Table'[Budget]))

     

     

     

    Best Regards,

    Stephen Tao

     

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

4 Replies

  • marapdq1993's avatar
    marapdq1993
    Frequent Visitor
    5 years ago

    I think I was able to make the formula work using ALLEXCEPT in the above statement if [COMPONENT] is not equals to CBD. However, my issue now is that the future-dated budget values (those with no actuals yet) are not showing. Is there something I am probably doing wrong?

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    marapdq1993 Seems like you have the right idea. A SWITCH(TRUE()...) statement might be cleaner depending on how many conditions you have. Having a bit of trouble following exaclty what you are trying to do.

    • marapdq1993's avatar
      marapdq1993
      Frequent Visitor
      5 years ago

      Thanks Greg for the feedback! Apologies, I wasn't super clear earlier. Made some changes in the sample raw data. I hope it helps. Maybe to help summarize:

       

      Budget - M/N breaks are only available for component CDE

      Actual - M/N breaks are available for all components

       

      Desired output:

      If Component CDE is selected, both budget and actual are filtered by M/N, with future-dated months for the budget still showing

      If all components are selected or other components <> CDE, actual will be filtered depending on the M/N value selected, but budget will show the overall value, with future-dated months for the budget still showing

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi marapdq1993 ,

     

    You need to create a separate table with components.

     

    Create two measures

    FilteredAcutal = CALCULATE(SUM('Table'[Actual]),FILTER('Table',[Component] IN ALLSELECTED('Table (2)'[Component])))
    FilteredBudget = IF(SELECTEDVALUE('Table (2)'[Component])="CBD",CALCULATE(SUM('Table'[Budget]),'Table'[Component]="CBD"),SUM('Table'[Budget]))

     

     

     

    Best Regards,

    Stephen Tao

     

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