cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ovetteabejuela
Impactful Individual
Impactful Individual

Table(visual): Matrices on the Side(Left-to-Right) not Top-to-Bottom

Is there anyway I could get B instead of A, A is the normal setup for the table, matrix visuals

 

PowerBITable-MetricsOnTheSide.PNG

4 ACCEPTED SOLUTIONS
bblais
Resolver III
Resolver III

You could turn your measures into a dimension using the pattern discussed here:

 

View Solution in Original Post

 

Once this is done, you can add the measure name to the rows instead of the columns...

 

View solution in original post

For example, I created an inline static table with my measure names in it like this:

 

Metrics = DATATABLE("Measure",STRING,{{"Revenue"},{"Costs"},{"Gross Profit"}})

 

I already have separate measures set up for my revenue, costs, and gross profit.  I just added another measure I called Val that is basically a switch based on which measure name is filtered by the current row (when used in a matrix for example):

 

Val = SWITCH(LASTNONBLANK('Metrics'[Measure],1),"Revenue", [Revenue],"Costs",[Cost],"Gross Profit",[Gross Profit])

 

And then I created a matrix with state and Metrics[Measure] on my rows, date on my columns, and Val as the value:

Capture.PNG

 

 

 

 

 

View solution in original post

Yes, but you will need to specify the aggregation like so:

 

Val = SWITCH(LASTNONBLANK('Metrics'[Measure],1),"Revenue", SUM([Revenue FIELD not Measure]),"Costs",SUM([Cost FIELD not Measure]),"Gross Profit",SUM([Gross Profit FIELD not Measure]))

View solution in original post

I did find that I was getting rows with blank states and values as you can see by my last screenshot.  This is because the Metrics table has no relationships to my other tables, and my DAX measure wasn't handling that.  So I changed it to the following, basically making sure that Val only returns something if it's used along with the Metrics.Measure field in a visual AND it will result in at least one of the measures in my list having a value (I chose revenue because in my example I can't have any of the other measures without their being revenue for that sale.  You may need to check if any of your measures have a value in your case).

 

Val = IF(NOT(ISBLANK([Revenue])) && HASONEVALUE(Metrics[Measure]),
         SWITCH(LASTNONBLANK('Metrics'[Measure],1)
                , "Revenue", FORMAT([Revenue],"Currency")
                , "Costs", FORMAT([Cost],"Currency")
                , "Gross Profit", FORMAT([Gross Profit],"Currency")
                , "Markup", FORMAT([Markup Pct],"Percent")
               )
        )

View solution in original post

12 REPLIES 12
bblais
Resolver III
Resolver III

You could turn your measures into a dimension using the pattern discussed here:

 

View Solution in Original Post

 

Once this is done, you can add the measure name to the rows instead of the columns...

 

View solution in original post

For example, I created an inline static table with my measure names in it like this:

 

Metrics = DATATABLE("Measure",STRING,{{"Revenue"},{"Costs"},{"Gross Profit"}})

 

I already have separate measures set up for my revenue, costs, and gross profit.  I just added another measure I called Val that is basically a switch based on which measure name is filtered by the current row (when used in a matrix for example):

 

Val = SWITCH(LASTNONBLANK('Metrics'[Measure],1),"Revenue", [Revenue],"Costs",[Cost],"Gross Profit",[Gross Profit])

 

And then I created a matrix with state and Metrics[Measure] on my rows, date on my columns, and Val as the value:

Capture.PNG

 

 

 

 

 

View solution in original post

ovetteabejuela
Impactful Individual
Impactful Individual

Hi @bblais,

 

There's just one problem with this, not the solution you've shared but I think how PowerBI is handling this.

 

You shared me a screenshot of your sample, can you try to issue a FORMAT command on your values, say

 

Val = SWITCH(LASTNONBLANK('Metrics'[Measure],1),"Revenue", FORMAT([Revenue],"0.00"),"Costs",[Cost],"Gross Profit",[Gross Profit])

And then do a filter, maybe weekly or monthly and then apply a filter to the matrix.

 

Because in my case if I do all of the above, it doesn't filter, we'll it does in a way because it's not showing data on all other days not part of the filter but it does show the days that are not part of the filter.

 

For example I have a data between March 1 to 31st and if I do a filter on WE0318, it would still show me March 1 to 31 days but only WE0318 has data. Other days are still visible although blank.

 

If you remove the FORMAT command, everything works as expected.

 

My problem is I want to apply a percentage datatype and this behaviours prevents me from doing that.

 

I am not sure though if you get the same results as I am but that is what I am experiencing now.

It's working fine for me.  I added a markup to my Metrics table as follows:

 

Metrics = DATATABLE("Measure",STRING,{{"Revenue"},{"Costs"},{"Gross Profit"},{"Markup"}})

 

Then I changed my DAX formula for Val to the following to add the FORMAT and to change to doing the aggregation within the formula and not in other measures:

 

Val = SWITCH(LASTNONBLANK('Metrics'[Measure],1),"Revenue", FORMAT(SUM(Sales[Order Revenue]),"$0.00"),"Costs",FORMAT(SUM(Sales[Order Cost]),"$0.00"),"Gross Profit",FORMAT(SUM(Sales[Order Revenue])-SUM(Sales[Order Cost]),"$0.00"),"Markup",FORMAT(DIVIDE(SUM(Sales[Order Revenue])-SUM(Sales[Order Cost]),SUM(Sales[Order Cost])),"0.00%"))

 

When I add a date slicer to my report and filter down to only a few dates, it only shows those dates:

 

Capture.PNG

 

I did find that I was getting rows with blank states and values as you can see by my last screenshot.  This is because the Metrics table has no relationships to my other tables, and my DAX measure wasn't handling that.  So I changed it to the following, basically making sure that Val only returns something if it's used along with the Metrics.Measure field in a visual AND it will result in at least one of the measures in my list having a value (I chose revenue because in my example I can't have any of the other measures without their being revenue for that sale.  You may need to check if any of your measures have a value in your case).

 

Val = IF(NOT(ISBLANK([Revenue])) && HASONEVALUE(Metrics[Measure]),
         SWITCH(LASTNONBLANK('Metrics'[Measure],1)
                , "Revenue", FORMAT([Revenue],"Currency")
                , "Costs", FORMAT([Cost],"Currency")
                , "Gross Profit", FORMAT([Gross Profit],"Currency")
                , "Markup", FORMAT([Markup Pct],"Percent")
               )
        )

View solution in original post

ovetteabejuela
Impactful Individual
Impactful Individual

@bblais,

 

Pretty cool,!!!

 

That did get rid of days without value (irrelevant days).

ovetteabejuela
Impactful Individual
Impactful Individual

@bblais,

 

I have a followup question though, is there anyway I could use fields instead of measures, for example

 

Val = SWITCH(LASTNONBLANK('Metrics'[Measure],1),"Revenue", [Revenue FIELD not Measure],"Costs",[Cost FIELD not Measure],"Gross Profit",[Gross Profit FIELD not Measure])

Yes, but you will need to specify the aggregation like so:

 

Val = SWITCH(LASTNONBLANK('Metrics'[Measure],1),"Revenue", SUM([Revenue FIELD not Measure]),"Costs",SUM([Cost FIELD not Measure]),"Gross Profit",SUM([Gross Profit FIELD not Measure]))

View solution in original post

ovetteabejuela
Impactful Individual
Impactful Individual

I see, totally forgot about that!

 

Thanks again @bblais!!!

ovetteabejuela
Impactful Individual
Impactful Individual

@bblais,

 

FANTASTIC! It worked! You just made my day!

 

t h a n k   y o u ! ! !

 

Smiley Very Happy

ovetteabejuela
Impactful Individual
Impactful Individual

Yes, exactly what I needed. I just need to apply this to my data model and hopefully I get it to work! Thanks again this is much more helpful.

ovetteabejuela
Impactful Individual
Impactful Individual

Thanks for leading me to that, I think I got the idea. Sadly for the other resource mentioned in that thread the link is dead, the BI Pattern websites seems to be under construction.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors