Forum Discussion

Jonjon_99's avatar
Jonjon_99
Helper I
3 years ago

VAR with multiple return values

hi im new to power BI and would like to learn how to return multiple values based on my matrix column (month, year, quarter, etc.) i have dax that will return column total but it is only works for m...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
AlanP514's avatar
AlanP514
Post Patron
3 years ago

Hai Jonjon_99 
Can you please elaborate your question and what you are trying to achive and the expected output you are looking, if its possible please share pbix file using dummy data

  • Jonjon_99's avatar
    Jonjon_99
    Helper I
    3 years ago

    hi Alan, thanks for checking. i want to have a table calculated by year.

    it works in monthly but not in yearly

    • Jonjon_99's avatar
      Jonjon_99
      Helper I
      3 years ago

      result should be 100% aswell in yearly summary.

    • AlanP514's avatar
      AlanP514
      Post Patron
      3 years ago

      Hi Jonjon_99 
      Thank for sharing , recieved the data, now please explain your expected output in detailed way ,
      What you are trying to achive , i am not geeting by reading your previous

      • Jonjon_99's avatar
        Jonjon_99
        Helper I
        3 years ago

        Hi Alan, thanks again for your reply.
        i have sales type filter, for my units,sales and total percenatage of units and sales by column.
        right now, it is working in my first matrix if i want to calculate the pecnetage of column by monthly summary cause i have this DAX which is specific only for monthly summary.

        %Total Units =
        VAR CurrentMonth = MAX('Date'[MonthName])
        RETURN
        DIVIDE(SUM(SalesFact[Units]),
                                CALCULATE(SUM(SalesFact[Units]),FILTER(ALLSELECTED(SalesFact),RELATED('Date'[MonthName])=CurrentMonth)))
         

        now, i want also this filter to work with my yearly matrix. as you can see, calculation is incorrect because the total is >100%.

        hope it is clearer now. sorry that my english is not good. haha! apreciate your time and help!


        https://drive.google.com/drive/folders/1i7N8YWWwMvZHEU08rxWIRDBsaC_kz6sg?usp=sharing