Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Incorrect calculation of percent of grand total by day.

Hello everyone.

Here's a look at the first table. I have rows and rows have a Root area column with some values.

2021-09-13 16_05_43-Moscow CF - Rework - Power BI Desktop.png
The first table works like a matrix. Values ​​are counts without calculations.
The second table are the same numbers, but now displayed as a percentage of GT per day. Great table.
The third is a graph. It's like the second table, but now with a graph. It is not very convenient due to the fact that the lines without specifying the area are more than 70% and the distribution over other areas is not visible. But note that the numbers in the third table are the same as in the second.

Let's hide the lines without area.

BAM!
No display settings have been changed. The% of GT is still there. But look at the numbers. Where did 504Code come from 5%? It should be 17.
Exactly also with other areas.

What are these numbers? How can you conveniently get the correct graphs, without lines with an empty value, but displaying% of GT for that day?

2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@Anonymous you should create your own measure when you are calculating % because when you filtering rows, it is removing when calculating GT. If you always want % is to be the same even if rows are filtered then use following measure:

 

Sum = YourTable[YourValueColumn] )

% = DIVIDE ( [Sum], CALCULATE ( [Sum], REMOVEFILTERS ( YourTable[ColumnonRows] ) ) )

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

v-kelly-msft
Community Support
Community Support

Hi  @Anonymous ,

 

Using "Stacked column chart" instead,when you are using "100% Stacked column chart",the percentage calculation will be changed based on your filter in the legend fields.

Then create a measure as below:

 

Measure =
VAR _total =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Month].[Day] = MAX ( 'Table'[Month].[Day] ) )
    )
RETURN
    DIVIDE ( SUM ( 'Table'[Value] ), _total )

 

And you will see:

vkellymsft_2-1632302557675.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

Hi  @Anonymous ,

 

Using "Stacked column chart" instead,when you are using "100% Stacked column chart",the percentage calculation will be changed based on your filter in the legend fields.

Then create a measure as below:

 

Measure =
VAR _total =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Month].[Day] = MAX ( 'Table'[Month].[Day] ) )
    )
RETURN
    DIVIDE ( SUM ( 'Table'[Value] ), _total )

 

And you will see:

vkellymsft_2-1632302557675.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

parry2k
Super User
Super User

@Anonymous you should create your own measure when you are calculating % because when you filtering rows, it is removing when calculating GT. If you always want % is to be the same even if rows are filtered then use following measure:

 

Sum = YourTable[YourValueColumn] )

% = DIVIDE ( [Sum], CALCULATE ( [Sum], REMOVEFILTERS ( YourTable[ColumnonRows] ) ) )

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Yeah almost. Only I do not have a date-area-value, but one line at a time.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.