cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sunkari Member
Member

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.

 

2 REPLIES 2
v-ljerr-msft Super Contributor
Super Contributor

Re: Dynamic rows in Power BI matrix

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.

 

t1.PNG

 

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] )
)

r1.PNG

 

Just hope it can give you some hint. Smiley Happy

 

Regards

Sunkari Member
Member

Re: Dynamic rows in Power BI matrix

@Eric_Zhang: Any inputs on above requirement