Avoid summing percentages in a matrix hierachy

[Anonymous]

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

[PowerBIDine]

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.

[MFelix · Accepted 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.

[PowerBIDine]

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

[MFelix]

Hi @PowerBIDine ,

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.

[PowerBIDine]

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 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)

[MFelix]

Hi @PowerBIDine ,

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?

[PowerBIDine]

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

[MFelix]

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)

[PowerBIDine]

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)))

[MFelix]

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)
    )

[PowerBIDine]

Helo 

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

[MFelix]

Hello, 

How can I help you out?

[PowerBIDine]

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

Dim Table

Fact Table

[MFelix]

Hi @PowerBIDine

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())

[PowerBIDine]

Accepted ..Thanks a lot

[PowerBIDine]

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

[PowerBIDine]

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

[PowerBIDine]

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))))

[MFelix]

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.

[PowerBIDine]

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