cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ssze
Helper I
Helper I

Matrix full of measures - when formatting blank rows show up!

Hi everyone,

I have generated a matrix full of measures a bit like this 

ssze_0-1601511574712.png

 

and the result is brilliant, showing something like this:

ssze_1-1601511600089.png

However, i'm trying to format the values (e.g. Area to 1 decimal place, Tonnes rounded to nearest number etc)

 and so i attempted a format function:

ssze_2-1601511657888.png

and suddenly all these blank rows show up with no data (but is all the available dates in my date table which is one of the columns)

ssze_3-1601511720916.png

Does anyone know (1) is there another way to format my numbers in the matrix full of measures and / or why the table suddenly show so many empty rows?

 

4 REPLIES 4
Frkstr
Frequent Visitor

I ran into the same issue, using the LEN function to return blanks in case of zero characters worked for me:

 

VAR Result = .... (your measure that includes formatting)

 

RETURN

IF(LEN(Result)=0, BLANK(), Result)
amitchandak
Super User
Super User

@ssze , Difficult to tell without looking at data.

 

The formatting it changing it into text. That might have cause this issue. There was the solution proposed by Guyinacube using external tools. See if that can help

https://www.youtube.com/watch?v=vlnx7QUVYME

 

measure slicer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

thanks @amitchandak , i had a look at the youtube and it looks good (although i'm still struggling to wrap my head around it a bit, as the values are not showing as i want).

i tidied up my example and hope this works for a download:

https://drive.google.com/file/d/13WUUB3H8jkjeApp5P-kmnr_srXeJSx7B/view?usp=sharing

 

the top table has all the data i want,

ssze_0-1601588912915.png

 

but i want to format it to the table below (i have compiled all the measures into a matrix)so i can have 2 layer headers

ssze_1-1601588940540.png

 

however, i want to be able to format the columns differently 

e.g. area to 1 decimal place, 

volume tonnes to nearest digit, but t/ha to 1 decimal place, sales to $ etc etc.

 

Would really appreciate any feed back. Been going through google searches for ages now and can't come up with much more!

 

Cheers,

Hello,

 

i formatted already the first 6 columns, I let you do the rest 😉 

 

 

Measure =
SWITCH( TRUE(),
MAX(MYOB_Group[Measure Name])="Area", IF(sum(HarvestSummary[Area (ha)]) <> BLANK(), FORMAT(sum(HarvestSummary[Area (ha)]), "0.0"), BLANK() ),
MAX(MYOB_Group[Measure Name])="Volume",IF(sum(FLITS[Nett]) <> BLANK(), FORMAT(sum(FLITS[Nett]), "0"), BLANK() ),
MAX(MYOB_Group[Measure Name]) = "Volume (t/ha)", IF([Vol_tperha] <> BLANK(), FORMAT([Vol_tperha], "0"), BLANK()),
MAX(MYOB_Group[Measure Name]) = "Log Sales", IF([Calc_LogSales] <> BLANK(), FORMAT([Calc_LogSales] * -1, "($ 0)"), BLANK()),
MAX(MYOB_Group[Measure Name]) = "Log Sales ($/t)", IF([Calc_LogSales_per_ton] <> BLANK(), FORMAT([Calc_LogSales_per_ton] *-1, "($ 0.00)"), BLANK()),
MAX(MYOB_Group[Measure Name]) = "Logging", IF([Calc_Logging] <> BLANK(), FORMAT([Calc_Logging], "$ 0"), BLANK()),
MAX(MYOB_Group[Measure Name]) = "Logging ($/t)", [Calc_Logging_per_ton],
MAX(MYOB_Group[Measure Name]) = "Cartage", [Calc_Cartage],
MAX(MYOB_Group[Measure Name]) = "Cartage ($/t)", [Calc_Cartage_per_ton],
MAX(MYOB_Group[Measure Name]) = "Management ($)", [Calc_Mgt_Total],
MAX(MYOB_Group[Measure Name]) = "Management ($/t)", [Calc_Mgt_per_ton],
MAX(MYOB_Group[Measure Name]) = "Gross Stumpage", [GrossStumpage],
MAX(MYOB_Group[Measure Name]) = "Gross Stumpage ($/t)", [GrossStumpage_per_ton],
MAX(MYOB_Group[Measure Name]) = "Gross Stumpage ($/ha)", [GrossStumpage_per_ha],
MAX(MYOB_Group[Measure Name]) = "Roads & Landings", [Calc_Roads_Total],
MAX(MYOB_Group[Measure Name]) = "R&L ($/t)", [Calc_Roads_per_ton],
MAX(MYOB_Group[Measure Name]) = "R&L ($/ha)", [Calc_Roads_per_hac],
MAX(MYOB_Group[Measure Name]) = "Other", [Calc_Other],
MAX(MYOB_Group[Measure Name]) = "Other ($/t)", [Calc_Other_per_ton],
MAX(MYOB_Group[Measure Name]) = "Net From Harvest", [Calc_Net],
MAX(MYOB_Group[Measure Name]) = "Net From Harvest ($/t)", [Calc_Net_per_ton],
MAX(MYOB_Group[Measure Name]) = "Net From Harvest ($/ha)", [Calc_Net_per_ha],
BLANK()
)
 
 
If this solves your problem, please mark it as the solution so others can find it.
 
 
Regards

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!