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
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 | 148k | 159k | - 52,5% | |
Cars | 100k | 110k | + 10,0% | |
Boats | 40k | 45k | + 12,5% | |
Other Motors | 8k | 2k | - 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
Solved! Go to Solution.
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
Proud to be a Super User!
Check out my blog: Power BI em Português
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
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......
also when i have selected "IN Process" or "Open" Seperately it is working , but when selected both it is showing Wrong values.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHelo Experts
I have created Measure for %
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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 source | Measure | |||
CSO | BU | A | B | % PPY |
Australia | BL Bosch blue | 0.85 | ` | A/B |
GR Bosch green | 0.15 | 0.50 | A/B | |
LG Garden | 0.21 | 0.69 | A/B | |
AC Accessories | 0.88 | 1.85 | A/B | |
SV Spare parts | 0.04 | 0.12 | A/B | |
DR Rotary tool | 0.26 | 1.48 | A/B | |
OT Others | 0.00 | -0.05 | A/B | |
SK Skil | 0.00 | 0.00 | A/B | |
IW Products | 0.00 | 0.00 | A/B | |
MT Measuring Tools | 0.27 | 0.69 | A/B | |
Total | SUM(A-col) | SUM(B-col) | SUM(A-col / SUM(B-col) | |
How I Matrix Given | ||||
Direct Column from source | Measure | |||
CSO | BU | A | B | % PPY |
Australia | BL Bosch blue | 0.85 | ` | A/B in % |
GR Bosch green | 0.15 | 0.50 | A/B in % | |
LG Garden | 0.21 | 0.69 | A/B in % | |
AC Accessories | 0.88 | 1.85 | A/B in % | |
SV Spare parts | 0.04 | 0.12 | A/B in % | |
DR Rotary tool | 0.26 | 1.48 | A/B in % | |
OT Others | 0.00 | -0.05 | A/B in % | |
SK Skil | 0.00 | 0.00 | A/B in % | |
IW Products | 0.00 | 0.00 | A/B in % | |
MT Measuring Tools | 0.27 | 0.69 | A/B in % | |
Total | SUM(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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
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]))
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
Kindly see below measure , Last logice for SAO is not working, could you help me
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHelo
Have a good day, i Need your support could you please help me
Hello,
How can I help you out?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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
Dim Table
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsAccepted ..Thanks a lot
Dear Power BI Expert,
I have a date between Slicer ,
Also have one Slicer
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
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
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
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYour 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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |