Forum Discussion

Sunkari's avatar
Sunkari
Responsive Resident
9 years ago

Dynamic rows in Power BI matrix

Hi All,

 

Requirement: We need to create a matrix visual which contains country on rows, Budget & Month on columns. As Year is a mandatory filter, matrix always shows data for one year.Refer the below structure to get more context about it.

 

 Month        
CountryJanFebMarAprilMay.......DecAvgYTD
India         
Pak         
US         
UK         

 

Need help of calculating Avg and YTD columns, these should be part of Month column.  Could you please provide possible solutions for this.

 

Need Help
Tips and Tricks

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi Sunkari,

    Need help of calculating Avg and YTD columns, these should be part of Month column.

    I don't think there is an easy way to do this with the current Matrix visual.

     

    I have tried to create a new table which contains the month name value, "Avg" and "YTD" like below. And create a relationship between the new table and the Date table with MonthName.

     

     

    However, I was not able to figure out how to calculate Avg, YTD for each country, as there is no related country value for "Avg" and "YTD" in each Matrix row.

    Measure =
SWITCH (
    FIRSTNONBLANK ( MonthTable[Month], 1 ),
    "Avg", [Avg] + 0,
    "YTD", "YTD",
    SUM ( Sales[Revenue] )
)

     

    Just hope it can give you some hint. :smileyhappy:

     

    Regards