cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bblais Resolver III
Resolver III

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

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

 

bblais Resolver III
Resolver III

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

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

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

@bblais,

 

Pretty cool,!!!

 

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors