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

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

 



  • AlanP514's avatar
    AlanP514
    Post Patron
    3 years ago

    Hi Jonjon_99 


    Can you please Check these tables 
    Above you can see %total units with months
    Down you can see %total units with year and it is not showing above >100 %, please confirm 

     

    • Jonjon_99's avatar
      Jonjon_99
      Helper I
      3 years ago

      Hi Alan,

      thanks for checking. still not correct.

      this is the result i am expecting.