Issue calculating percentage of total

[GuyJohnson]

I’m having a bit of an issue trying to calculate a percentage in my Power BI Model.

I’m not looking for someone to write the DAX for me but to get some insight into anything Sam or someone else has released that could help me. I’ll learn it better if I do it my self rather than copy your DAX.

My issue (no pbix) is this:
I have a Spreadsheet that I can count the rows on to get a total.
I have a second column that has entries for Order, Reorder, Trial and blank entries (blanks are unimportant)
I need to calculate the percent of Order, Reorder and Trial entries and be selectable by a Slicer

I know Sam has done this in the past but my searching is coming up blank.

Thanx

Guy

[BrianJ]

@GuyJohnson,

Try these:

Percent of Total - simple

Percent of Total - context considerations

• Brian

[GuyJohnson]

Thanx @BrianJ I’ll give these a look this afternoon

Guy

[BrianJ]

@GuyJohnson,

I know he also deals with this issue in the ALL and ALLSELECTED videos, but start with the two above, and if those don’t solve your issue, this would be the next place to look…

• Brian

[GuyJohnson]

The 2 videos you referenced solved my issue within 1 minute (as I bang my head on the desk)

Guy

[GuyJohnson]

Moving a little further into my Model

How do I remove the blue edging from my theme. Don’t want the blue overall blue but it looks terrible in the Desktop (Display1) and the Service (Display2

Guy

Guy

[BrianJ]

@GuyJohnson,

Make sure no visuals are selected, then go to the format options (paint roller) and select page background:

then scroll down to where the image is and click “x” to clear the background:

• Brian

[Greg]

Very difficult to say without a PBIX @GuyJohnson, but if your report uses a Page Background image, I’d cycle through the Image Fit options and see if that helps. Greg

[GuyJohnson]

@BrianJ @Greg

Neither one of the suggestions work. I like the waves in the background and the green color. What’s bugging me is the blue border that I can’t get rid of.

PBIX attached Quote Logs.pbix (958.9 KB)

Guy

[AntrikshSharma]

That’s coloring over the wallpaper, and the blue border is what you won’t see when you publish the report.

[GuyJohnson]

No the blue is very pronounced when published

[Greg]

It’s the page wallpaper, @GuyJohnson. I’ve never used wallpaper before this moment (only backgrounds), but after one quick web search, page wallpaper is larger than the page background. I can “eliminate” the border by setting the transparency of the background to 100%, but now everything is all blue. If you remove the image from the wallpaper and just use it in the background instead, then use a full-screen rectangle with your green and 50% transparency, you should be good (I don’t have time to try this out myself right now). Good luck. Greg

[GuyJohnson]

Thanx @Greg

I picked it from he themes in the view tab. Just liked the way it looked.

Time to go basic color I guess.

Guy

[Greg]

You can use PowerPoint as well to create a background you like, with all the overlays and transparencies you like, then export as an image, then use this as your page background … I’ve found this works quite well and use it frequently. Greg

[GuyJohnson]

I’ll give that a try early next week, for now I dropped an image of the local plant that I’m referencing at 60% transparency and selected fit and it looks good.

I was just trying to use one of Microsoft’s themes to begin with for a bit of a change from plain colors.

Guy

[Greg]

A PBIX file is actually just a zip file, so you can unzip it and extract the original image from the resulting sub folder if you prefer. Greg

[BrianJ]

@GuyJohnson,

Okay, got it to work – green, no borders.

This turns out to be unexpectedly tricky. Here’s how I did it:

1. I used @Greg’s zip file trick to try to find the image file. That was helpful in that it let me burrow into the directory structure of the file, but no PNGs or JPGs were found.
2. Turns out they encode the wallpaper file as binary 64 in the JSON theme file. I copied the binary 64 code into an online decoder to turn it back into a graphics file:

3. Then in SnagIt, I did a color replace of the blue with the green color you wanted:

4. Then cleared out the wallpaper settings in PBI, and used the color replaced green image in the background settings, set to “Fit”

Hope this gets you what you need. I’ve attached the original decoded background, the green background and the updated PBIX file if you want to make any additional changes.

Turned out to be a far more interesting problem than I think any of us originally thought…

• Brian

Quote Logs - color fix solution.pbix (1.3 MB)

[JarrettM]

@GuyJohnson,

Here is a great video from Sam on using PowerPoint to create your page background. I use this technique in all my models.

https://m.youtube.com/watch?v=HV0EL01lkvc

Thanks
Jarrett

[GuyJohnson]

Thanx much to @BrianJ for going above and beyond to solve this issue.

Never expected this. Will file this away for the next time it raises it’s head.

Guy

[BrianJ]

@GuyJohnson,

My pleasure – glad that was helpful to you.

It ended up working out great for me too, since it took a failed attempt at something I tried to do in the Data Challenge #5, and turned it into something useful I could do with the information. I actually referenced this problem in my postmortem writeup.

• Brian