Forum Discussion

JOAP's avatar
JOAP
Helper I
7 years ago
Solved

Retrieve filtered total on all rows in table visual using DAX measure

I'm a beginner using DAX in Power BI Desktop and I'm currently struggling with what in the end will be a % calculation. In short I have a large FinanceFacts table (data) where I have Income Statement accounts without any summarization levels. For example sum of Total Sales is instead found in another table named IS_Nolato_1 and this table is building the structure for an Income Statement Report. I want to have my DAX measures in separate tables from tables containing data.

 

 

So I'm getting a nice table visual (filtered) of that Income Statement Report with some summarization levels but also want to calculate a DAX measure: Amount_YTD_Prod_Sales and that I succeed with:

 

Amount_YTD_Prod_Sales = CALCULATE('Measures Amounts'[Amount_YTD];FinanceFacts[Account_Level0]="IS3000")+CALCULATE('Measures Amounts'[Amount_YTD];FinanceFacts[Account_Level0]="IS3100")
 
However my issue is that the above DAX measure is only shown on rows in table visual (from table: IS_Nolato_1) where the accounts "IS3000" and "IS3100" are included in those summarized levels and I want that value on all rows to be able to calculate % of? Why are the yellow rows below without the number 91,168.00?
 
 
If I do this directly on the table FinanceFacts it works fine.
 
 
It is probably easy to fix and I have been trying to find some ideas in this Forum but failed so far. 
Need Help
  • JOAP's avatar
    JOAP
    7 years ago

    Hi Zoe,

    Many thanks for getting back to me.

    I did some more thinking and decided to change the model regarding Income Statement forms by putting them into a new common table named IS_Forms and in that table I will collect my different needs for Income Statements. By doing that I was able to make the % calculation on my table visual in the row context even more sophisticated.  Took me a while to understand and find SELECTEDVALUE which turned out great. Please see the new DAX measure below:

     

     

    Best Regards,

    JOAP

4 Replies

  • JOAP's avatar
    JOAP
    Helper I
    7 years ago

    Solved it myself with: 

    %_of_Prod_Sales_YTD = DIVIDE('Measures Amounts'[Amount_YTD];CALCULATE('Measures Amounts'[Amount_YTD_Prod_Sales];ALL(IS_Nolato_1));0)
     
    :smileyhappy:
    • JOAP's avatar
      JOAP
      Helper I
      7 years ago

      However that solution will only work for table IS_Nolato_1 and therefore cannot be used as a general measure for other income statements etc? So I am not 100% happy about it and such a measure might as well be placed under the IS_Nolato_1 table.

      • dax's avatar
        dax
        Community Support
        7 years ago

        Hi JOAP,

         

        It seems that you solve above problem, right? But you said that  “However that solution will only work for table IS_Nolato_1 and therefore cannot be used as a general measure for other income statements etc? ” If you want to apply this measure to other visual or table, you could write similar logic measure for other table. Or what requirement did you want to get? If possible, could you please inform me in details(such as your sample date or upload your pbix)?

         

         

        Best Regards,

        Zoe Zhi

         

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