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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Avoid summing percentages in a matrix hierachy

Hi, 

I'm working on a project where I have a hierachy of products e.g. Product class = Motors, Product type = {Cars, Boats ...}.  I've created a table where I have information on how well we're doing in sales on each product type as compared to previous years. Adding this to a matrix, POWER BI automatically sums each product type to give the total for a product class (Table below). 

Product CLASS  Product TYPE  Last year  This year  % change  
Motors 148k159k- 52,5%
 Cars100k110k+ 10,0%
 Boats40k 45k+ 12,5%
 Other Motors8k2k- 75,0%


This is fine for the sales, but it also sums the percentages, which gives the wrong toalt percentage for the class. (Red number above, should've been + 7.4%).

Is there any way to fix this? I've been trying for quite a while, but I can't find a solution. Thanks for taking the time to read this 🙂 
Kind regards, Fredrik

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @Anonymous ,

 

In this case you have to make a measure to calculate the percentage and not a calculated column.

 

Use the following measure:

% Change = 1 - DIVIDE(SUM(Table[This Year]), SUM(Table[Last Year]))

Then format has percentage should give correct result.

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

23 REPLIES 23
Anonymous
Not applicable

 

Dear Experts,

Kindly see my Measure , and all Filter , My measure is working well and give the expected results.

when not selected anything in "Life category" FILTER , 

Measure Logic :-   Based on String ( based on once cell whole text, wherever  it occurs we have to count and show it the individual text aganist

 

PowerBIDine_0-1715838055882.png

 

But when i have selcted the "Life Cycle status" , it is showing wrong values, if not selected the Filter , that time also there is only this 2 category , but when i have selected manually 2 items , it is showing wrong number based on Competitor wise......

PowerBIDine_1-1715838413791.png

also when i have selected "IN Process" or "Open" Seperately  it is working , but when selected both it is showing Wrong values.

 

 

 

MFelix
Super User
Super User

Hi @Anonymous ,

 

In this case you have to make a measure to calculate the percentage and not a calculated column.

 

Use the following measure:

% Change = 1 - DIVIDE(SUM(Table[This Year]), SUM(Table[Last Year]))

Then format has percentage should give correct result.

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Helo Experts

 

I have created Measure for % 

% MTD TGS PY vs PPY1 = Var  a = CALCULATE(SUM(vPBI_PT_Daily_Snapshot[MTD PY TGS IC]))
Var b =  CALCULATE(SUM(vPBI_PT_Daily_Snapshot[MTD PPY TGS IC]))
Var ic = 1- (DIVIDE(a,b)-1)
Var   c = CALCULATE(SUM(vPBI_PT_Daily_Snapshot[MTD PY TGS LC]))
Var  d = CALCULATE(SUM(vPBI_PT_Daily_Snapshot[MTD PPY TGS LC]))
Var lc = 1- (DIVIDE(c,d)-1)
Return
SUMX(vPBI_PT_Daily_Snapshot,IF(vPBI_PT_Daily_Snapshot[CSO] = "AU61" , ic , lc))
 
Every individual Rows showing Correct Results , but in ROW Subtotal....the Percentage also Sumimg up so got wrong results , could any one help this
 
 
PowerBIDine_0-1697010115058.png

 

Hi @Anonymous ,

 

What is suppose to be the result in this case for the total line the average value of the previous rows?

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi Thank you for quick responding,

 

Matrix Table...Sum up the Percentage column in Subtotal line... 

Just Focus on PPY% Colum where Measure sum up

 

How I want
  Direct Column from sourceMeasure
 CSOBUAB% PPY
AustraliaBL Bosch blue0.85`A/B
 GR Bosch green0.150.50A/B
 LG Garden0.210.69A/B
 AC Accessories0.881.85A/B
 SV Spare parts0.040.12A/B
 DR Rotary tool0.261.48A/B
 OT Others0.00-0.05A/B
 SK Skil0.000.00A/B
 IW Products0.000.00A/B
 MT Measuring Tools0.270.69A/B
 TotalSUM(A-col)SUM(B-col)SUM(A-col / SUM(B-col)
     
     
How I Matrix Given
  Direct Column from sourceMeasure
 CSOBUAB% PPY
AustraliaBL Bosch blue0.85`A/B in %
 GR Bosch green0.150.50A/B in %
 LG Garden0.210.69A/B in %
 AC Accessories0.881.85A/B in %
 SV Spare parts0.040.12A/B in %
 DR Rotary tool0.261.48A/B in %
 OT Others0.00-0.05A/B in %
 SK Skil0.000.00A/B in %
 IW Products0.000.00A/B in %
 MT Measuring Tools0.270.69A/B in %
 TotalSUM(A-col)SUM(B-col)SUM(%PPY-col)

 

 

Hi @Anonymous ,

 

Looking at your explanation this should be a simple SUMX however you are doing a IF statment on your calculation SUMX(vPBI_PT_Daily_Snapshot,IF(vPBI_PT_Daily_Snapshot[CSO] = "AU61" , ic , lc)) What does this CSO filter impacts the calculation?

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi,

Have you checked my Screen shots......my percentage calculation.......in Matrix table automatically giving Sub total right?? if there is a sales number or amount there will not be an issue....but in case if there is Percentage calculation that time also  Matrix Table given.......Suming the Percentage  (in subtotal line).... here ( in subtotal line) i need to apply the same Percenateg logic which is created by me as a measure....hope you got my point...pls ref above 3 post

 

If I can understand corretly you want to have the % in all the rows even totals for the data you gave I did the following measure:

Measure = DIVIDE(SUM('Table'[A]), SUM('Table'[B]))

MFelix_0-1697020396060.png

In your metric what I thing is happening is the SUMX part try to change your measure to:

I have created Measure for % 

% MTD TGS PY vs PPY1 = Var  a = CALCULATE(SUM(vPBI_PT_Daily_Snapshot[MTD PY TGS IC]))
Var b =  CALCULATE(SUM(vPBI_PT_Daily_Snapshot[MTD PPY TGS IC]))
Var ic = 1- (DIVIDE(a,b)-1)
Var   c = CALCULATE(SUM(vPBI_PT_Daily_Snapshot[MTD PY TGS LC]))
Var  d = CALCULATE(SUM(vPBI_PT_Daily_Snapshot[MTD PPY TGS LC]))
Var lc = 1- (DIVIDE(c,d)-1)
Return
IF(vPBI_PT_Daily_Snapshot[CSO] = "AU61" , ic , lc)

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi,

 

Kindly see below measure , Last logice for SAO is not working, could you help me

 

 

mEUR YTD PY = var a = CALCULATE(SUM(EVA[1EUR=AUD PY]))
Var b = CALCULATE(SUM(EVA[1EUR=NZD PY]))
Var c = CALCULATE(SUM(vPBI_PT_Daily_Snapshot[YTD PY TGS LC]),vPBI_PT_Daily_Snapshot[CSO] = "AU60")
Var d = CALCULATE(SUM(vPBI_PT_Daily_Snapshot[YTD PY TGS IC]),vPBI_PT_Daily_Snapshot[CSO] = "AU61")
Var Aud = c/a
Var Nz = d/b
Return
IF(SELECTEDVALUE(vPBI_PT_Daily_Snapshot[CSO]) = "AU60" , Aud , IF(SELECTEDVALUE(vPBI_PT_Daily_Snapshot[CSO]) = "AU61" , Nz , IF(SELECTEDVALUE(vPBI_PT_Daily_Snapshot[CSO]) = "SAO" , Aud + Nz)))

Not sure if this works but try this:

 

mEUR YTD PY =
VAR a =
    CALCULATE ( SUM ( EVA[1EUR=AUD PY] ) )
VAR b =
    CALCULATE ( SUM ( EVA[1EUR=NZD PY] ) )
VAR c =
    CALCULATE (
        SUM ( vPBI_PT_Daily_Snapshot[YTD PY TGS LC] ),
        vPBI_PT_Daily_Snapshot[CSO] = "AU60"
    )
VAR d =
    CALCULATE (
        SUM ( vPBI_PT_Daily_Snapshot[YTD PY TGS IC] ),
        vPBI_PT_Daily_Snapshot[CSO] = "AU61"
    )
VAR Aud = c / a
VAR Nz = d / b
RETURN
    SWITCH (
        SELECTEDVALUE ( vPBI_PT_Daily_Snapshot[CSO] ),
        "AU60", Aud,
        "AU61", Nz,
        "SAO",CALCULATE( Aud + Nz)
    )

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Helo 

Have a good day, i Need your support could you please help me

Hello, 

 

How can I help you out?


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi all,

 

I have Dim Table (used Distinct Function) and fact table When try to make a relationship between these 2 table , PowerBI Suggested only Many to many.

 

How to Fix this , there is no Duplicate in Dim_Table also I have used DISTINCT function

 

Please help on this

 

PowerBIDine_0-1712417167208.png

 

 

 

Dim Table

PowerBIDine_1-1712417167212.png

 

 

 

Fact Table

 

 

Hi @Anonymous

 

The problem is the blank row at the  top you need to remove the blank. 

 

Dimsalestable = FILTER(DISTINCT('Opportunitie Pipeline' [Sales Team]),[Sales Team]<>BLANK())

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Accepted ..Thanks a lot

Anonymous
Not applicable

Dear Power BI Expert,

 

 

I have a date between Slicer ,

 

PowerBIDine_0-1706015608525.png

 

 

Also have one Slicer

 

PowerBIDine_1-1706015608525.png

 

 

My Requirement is , When I choose the MTD , Date Range should select Current Month First date to today date ,  Likewise for QTD & YTD.

 

I have written below Measure

 

Slicer Cont = Var a = IF(MONTH(SELECTEDVALUE('Transaction_details'[CalDay])) = MONTH(TODAY()),"1")

Var b = IF(QUARTER(SELECTEDVALUE('Transaction_details'[CalDay])) = QUARTER(TODAY()),"1")

Var c = IF(YEAR(SELECTEDVALUE('Transaction_details'[CalDay])) = YEAR(TODAY()),"1")

Return

IF(SELECTEDVALUE(Period[Period]) = "MTD" , a , IF(SELECTEDVALUE(Period[Period]) = "QTD" , b ,IF(SELECTEDVALUE(Period[Period]) = "YTD" , c)))

 

 

This Measure was Working with Slicer Model ( Drop Down) ,

 

But it is not working with Date range Between Slicer Model,

 

 

Could you help me in this Regards

Anonymous
Not applicable

Date between Slicer Model, in this case there is Another Slicer for MTD , QTD, YTD, 

If Comes to date Between Slicer, when i Choose MTD Slicer , the Date Between Slicer Showing Starting date and end date for MTD, like wise for QTD and YTD,

could u pls help me

Anonymous
Not applicable

Hi

 

For SAO if i given output as ZERO(0) it is coming but while giving that Logic var Aud or Var Nz or Aud + Nz it is showing only blank againsy SAO Summary,,,,Could you please help,,,tomm i have to deliver my project

 

But below is another one measure with SAME logic....it is working 😞  kindly

 help 

PY mEur* = var a = calculate(MAX(EX_RATES_CFFIT_LIN[ExRateAvgYTD]),EX_RATES_CFFIT_LIN[CurrencyKey]="AUD",EX_RATES_CFFIT_LIN[Year]=2021)
var b = calculate(max(EX_RATES_CFFIT_LIN[ExRateAvgYTD]),EX_RATES_CFFIT_LIN[CurrencyKey]="NZD",EX_RATES_CFFIT_LIN[Year]=2021)
var c = CALCULATE(sum(sia[MTD PY TGS IC]),sia[CSO]="AU60")
var d = CALCULATE(sum(sia[MTD PY TGS IC]),sia[CSO]="AU61")
var e =c/a
var f = d/b
return
if(SELECTEDVALUE(sia[CSO])="AU60",c/a,if(SELECTEDVALUE(sia[CSO])="AU61",d/b,if(SELECTEDVALUE(sia[CSO])="SAO",(e)+(f))))

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Your understanding is Perfect, but based on your Sugeestion if i removed SUMX then Table Column name does not showing in IF Condition , unable to select , it is not showing.....Only required column name is showing when added SUMX, thats my issue

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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