Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Create an Average row in matrix.

Hi All,   Thanks in advance. The following is where I am at when I try to design a custom dashboard.   I used data from three sources to build a matrix table. Under the total row, I also require ...
Need Help
Show and Tell
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi, 

    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

    expected result: = 
VAR _sum =
    SUM ( data_fact[value] )
VAR _total =
    CALCULATE ( SUM ( data_fact[value] ), ALL ( matrix_dim ) )
VAR _monthlyaverage =
    AVERAGEX (
        ALL ( matrix_dim[Year-Month sort number], matrix_dim[Year-Month] ),
        CALCULATE ( SUM ( data_fact[value] ) )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( matrix_dim[Year-Month] ),
        "Total", _total,
        "Average", _monthlyaverage,
        _sum
    )

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
1 year ago

Hi, 

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

 

 

 

expected result: = 
VAR _sum =
    SUM ( data_fact[value] )
VAR _total =
    CALCULATE ( SUM ( data_fact[value] ), ALL ( matrix_dim ) )
VAR _monthlyaverage =
    AVERAGEX (
        ALL ( matrix_dim[Year-Month sort number], matrix_dim[Year-Month] ),
        CALCULATE ( SUM ( data_fact[value] ) )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( matrix_dim[Year-Month] ),
        "Total", _total,
        "Average", _monthlyaverage,
        _sum
    )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thnank you. I have not tried writing this complex formulas in POwer bi. I will try with the above.