Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 :
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |