Format Question

[mlmagl14]

Hello,

I am trying to see if I can get my report in the example format I created in excel. I need to get the "% of sales" and "% of Q sold"

placed as a row under the totals and not in columns. Is that possible in power bi because when I add those fields under values it places them under its own column. Here is the example for the format I want and the format I currently have on power bi. 

Format I wantFormat I haveVisualization field

[mahoneypat · Accepted solution]

Here is one way to do it. You can use an IF to detect if you are in the total row and, if so, create a string that includes a carriage return and two numbers (the total and the % of overall total). Measure for this example shown below.

PctInTotalOnly =
VAR totsales = [Total Sales]
RETURN
IF (
COUNTROWS ( DISTINCT ( 'Product'[Product] ) ) > 1,
totsales & UNICHAR ( 10 )
& FORMAT (
DIVIDE ( totsales, CALCULATE ( [Total Sales], ALL ( 'Date' ) ) ),
"0.0%"
),
totsales
)

Pat

[mlmagl14]

Awesome! Thanks so much Pat for helping me out. I'm still very unfamiliar with DAX formulas, so I'm really sorry for my lack of knowledge. I pretty much copied your DAX formula for my report and I think I understand most of the dax function you entered, except for the part of where  in the dax function the percentage is coming from. I'm not really sure where the formula is pulling the percentages from. 

The DAX I entered following your example : 

PctinTotalOnly = VAR totsales=[Sales Total] Return IF(COUNTROWS(DISTINCT('3 categories'[Product Type]))>1, totsales & UNICHAR(10) & FORMAT(DIVIDE(totsales,CALCULATE([Sales Total], ALL ('Date'[Week Number]))), "0.0%"), totsales)

The results for the percentages were off. Like for week 33, the % of Sales should be 22% if its dividing from the total sales from the report (16301.18 week total divided by 73679.04 overall total sales) but it comes out to 11.1% with the DAX. Can you explain to me how to get the percentage? I actually wanted to divide the weekly total by 114371 to get the weekly % of sales , which is the true overall total (number from an outside report) 

The report I got with the DAX formula

[mahoneypat]

I wasn't sure what you were using for your total, so just jused that expression to get all weeks. I don't see the #s you reference in your image (73,679.04 or 114371). If you have measures that return those #s, just use that measure in the denominator part of the DIVIDE function instead.

Pat