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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
SabineOussi
Skilled Sharer
Skilled Sharer

Monthly Percentage

Hello,

 

Following this thread
http://community.powerbi.com/t5/Desktop/Monthly-Comparison/m-p/22548
Now I want to add the percentage each value constitutes of the whole month.

 

I created a simlpe column Percentage = MonthlyValue/SUM(MonthlyValue) but when putting it in my visual (matrix), it calculates the portion of the value over all values providede in all months and not only the month in question.

 

How can I replicate the attached screenshot in Power BI?

Monthly Report - Percentage.PNG

 

Thank you.

1 ACCEPTED SOLUTION

The difference is, that my [MonthlyValue] is e measure, and yours is the column name without aggregation. Put a SUM() around your "Table13[Monthly Value]" --> SUM( Table13[Monthly Value] ).

 

The complete formula look like this:

 

=SUM( Table13[Monthly Value] ) / 

CALCULATE(

   SUM( Table13[Monthly Value] ),

   ALL(Tabelle13[Column1])

)

 

Does it work now? Is column1 the column with the customer?! Never use Columns without an aggregator (eg. SUM())!

 

Regards,

Lars

View solution in original post

9 REPLIES 9
SabineOussi
Skilled Sharer
Skilled Sharer

Thank you for the follow up @LarsSchreiber!
It still did not work as a column even when adding SUM() but it worked as a measure. These two types still get me very confused.

Merci @Anonymous

Anonymous
Not applicable

Hi @SabineOussi,

 

   the difference between calculated columns and measures is :

 

  • A Calculated Column runs the calculation on each individual row.  The calculation happens BEFORE aggregating.
  • A Calculated Measure runs the calculation on a collection of rows.  Calculations happen AFTER aggregating.

 

If you need more information this is the source.

Anonymous
Not applicable

Hello,

 

I'm sorry my previous post was incomplete, I've updated it now 🙂

You're Welcome @SabineOussi and sorry for the delay

Sebastien

 

Hi @SabineOussi,

 

try the following formula for the percentages:

 

Pct = [MonthlyValue] /

          CALCULATE(

             [MonthlyValue];

             ALL(Tabelle1[Customer])

          )

 

Regards,

Lars

Hi @LarsSchreiber,

 

Sorry but it did not work, wrong values.

 

Any other approach?

Hi @SabineOussi,

 

did you use your Table-Names and Column Names???... In my case the table name is "Tabelle1"... The formula worked fine in my model.

 

The main point is the ALL()-function within the CALCULATE().

 

Regards,

Lars

This is what I tried and got.

Pct.PNG

 

Please advise if there was anything wrong.
I even tried sum and count after calculate but none was right.

The difference is, that my [MonthlyValue] is e measure, and yours is the column name without aggregation. Put a SUM() around your "Table13[Monthly Value]" --> SUM( Table13[Monthly Value] ).

 

The complete formula look like this:

 

=SUM( Table13[Monthly Value] ) / 

CALCULATE(

   SUM( Table13[Monthly Value] ),

   ALL(Tabelle13[Column1])

)

 

Does it work now? Is column1 the column with the customer?! Never use Columns without an aggregator (eg. SUM())!

 

Regards,

Lars

Anonymous
Not applicable

Hello @SabineOussi

 

The last @LarsSchreiber formula is working pretty well !

 

Here's the screenshot of what I did :

The model :

Model.png

 

the formula 

Formula.png

 

And the result :

Chart.png

 

Sébastien

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.