Hello All,
Please help me with this.
Revenue is a Default meausre from SQL server source.
I calulated Revenue Contribution(Measure) as DIVIDE(SUM(Query1[Revenue]),[Total Revenue],0)
then after sorting the Contribution I tried calculating another Measure as follows but earlier is not suppose to show 'Contribution' measure while i am typing
MAXX(Query1,CALCULATE([Contribution],[Contribution] >= EARLIER("I want Contribution Measure here"),ALL(Query1)))
for running total (the calculation i got from this post )
But this is possible with Revenue which is a default Measure from source.
MAXX(Query1,CALCULATE(SUM(Quer1[Revenue]),Quer1[Revenue] >= EARLIER(Query1[Revenue]),ALL(Query1)))
Is there any way to pass 'Contribution' as argument to 'Erliler' function
or Calculating Contribution at sequel level and bringing it as a default measure as revenue is only the way.
I dont want to
Thanks in advance.
Solved! Go to Solution.
I wrote a blog post today about how to create a Pareto Cumulative Running Total
http://exceleratorbi.com.au/cumulative-running-total-based-on-highest-value/
Are you writing a measure or a calculated column?
What defines the order of the running total? Do you have an ID column in your data table, or a date column or something?
Do you have a calendar table?
How are you planning on visualising this? In a Pivot table with some ID column on rows and the running total in values?
Everything is a Measure.
And Revenue is a Default measure from Fact.
I want output like this.
After descending order of Contribution I will calculate Running Total.
Item Revenue Contribution RunningTotal
1 300 0.50 0.50
2 200 0.33 0.83
3 100 0.16 0.99
=calculate([contribution],filter (all(table),table[item] <=max(table[item])))
Negative @MattAllington..!!
how can we use '<= max(item)' condition, when Items are sorted in desccending order of contribution...??
what if I dont have Item Number and want to use Item Name from dimension, this max() function shouldn't work with strings as far as i know.
I just provided you a solution that works for the sample data you posted. If your data is not like the sample data you posted, then it won't work - of course.
Hi @MattAllington,
I apologize.
I am so sorry if I hurt you somehow.
My English is not that good, so please forgive me if I got wrong anywhere in my posts.
And you are far far away from me in the dax concepts and Power BI, in fact you are an author of a book for DAX.
you are like a teacher to me. I just started Power BI Directly a month ago,No power Query, No Power Pivot and No Power View.
I am very poor in all DAX and Power Concepts.
Please accept my apology and help me out in my problem if you come across any solution.
Thank You.
Hi @MattAllington @jahida and @v-micsh-msft
Here is the sample data and my Model.
DimItem
ItemKey | ItemNumber | Company Code |
1 | 101 | A |
2 | 101 | B |
3 | 101 | C |
4 | 102 | A |
5 | 102 | B |
6 | 102 | C |
7 | 103 | A |
8 | 104 | A |
9 | 104 | B |
10 | 104 | C |
DimCompany
Companykey | CompanyCode | CompanyName |
1 | A | Com1 |
2 | B | Com2 |
3 | C | Com3 |
DimCustomer
Customerkey | Customer Code | CustomerName |
1 | 11 | Cus1 |
2 | 12 | Cus2 |
3 | 13 | Cus3 |
4 | 14 | Cus4 |
5 | 15 | Cus5 |
Fact1 (Grouped By ItemNumber from DimItem)
Query: SELECT I.ItemNumber, sum(F.Revenue) FROM Fact F inner join DimItem I on I.ItemKey=F.ItemKey group by I.ItemNumber
ItemNumber Revenue
101 600
102 600
103 300
Now the Following Formula for Contribution and running totals are working fine:
I put all these in a table vis:
ItemNumber Revenue RevenueContribution(DESC Order) RunningTotal
TotalRevenue = CALCULATE(SUM(Fact[Revenue]),ALL(Fact))
RevenueContribution = DIVIDE(SUM(Fact[Revenue]),[TotalRevenue],0)
RunningTotal= MAXX(Fact,CALCULATE([RevenueContribution],Fact[Revenue] >= EARLIER(Fact[Revenue]),ALL(Fact)))
Same formulaes with Following Fact:
Fact2
Query: SELECT I.ItemKey,I.ItemNumber,CO.CompanyKey,CS.CustomerKey,sum(F.Revenue)
FROM Fact F
inner join DimItem I on I.ItemKey=F.ItemKey
inner join DimCompany CO on CO.CompanyKey=F.CompanyKey
inner join DimCustomer CS on CS.CustomerKey = F.CustomerKey
group by I.ItemKey,I.ItemNumber,CO.CompanyKey,CS.CustomerKey
ItemKey ItemNumber CompanyKey CustomerKey Revenue
1 101 1 1 100
2 101 2 1 200
3 101 3 2 300
4 102 1 3 400
5 102 2 1 200
Now the Following Formula for Running Totals are not working fine:
I put all these in a table vis:
ItemNumber Revenue RevenueContribution(DESC Order) RunningTotal
TotalRevenue = CALCULATE(SUM(Fact[Revenue]),ALL(Fact))
RevenueContribution = DIVIDE(SUM(Fact[Revenue]),[TotalRevenue],0)
RunningTotal= MAXX(Fact,CALCULATE([RevenueContribution],Fact[Revenue] >= EARLIER(Fact[Revenue]),ALL(Fact)))
I need second fact to build model and to filter reports according to the respective company and customer.
Means I want to take CompanyName from DimCompany and filter the report on it same with Customer.
Where am I lagging please help me guys.
Thanks in advance.
I wrote a blog post today about how to create a Pareto Cumulative Running Total
http://exceleratorbi.com.au/cumulative-running-total-based-on-highest-value/
no @MattAllington,
it didn't workout for my sample data.
please consider the following screenshot.
All Measures
Here
1. Contr = DIVIDE(SUM(Query1[Revenue]),[Total Revenue],0)
where Total Revenue = CALCULATE(SUM(Query1[Revenue]),ALL(Query1))
and the formula you suggested
RT = CALCULATE([Contr],FILTER(ALL(Query1),Query1[ItemNumber] <= MAX(Query1[ItemNumber])))
2.. Contribution = SQL generated (Revenue / sum(Revenue) ) from SQL Source
Running Total = MAXX(Query1,CALCULATE(SUM(Query1[Contribution]),Query1[Contribution] >= EARLIER(Query1[Contribution]),ALL(Query1)))
in which 2 is working fine .But i dont want to calculate Contribution at SQL level.
Hi Taumirza,
What is your current situation?
Based on what I know, Earlier function only accept A column or expression that resolves to a column to be the first parameter.
So generally measure can’t be used here, at least for the measure defined in Power BI.
For those measures defined under Data source side, that measure is evaluated when getting data from the data source, actually those measures should be treated as calculated column in Power BI.
To calculate the running total, we should either create an Index to help calculate, or take use of the earlier function with a calculated column.
Actually, both of your measures (1 and 2) works. In order to have the RT and the Running Total to have the same result, we need to sort the ItemNumber in ascending order.
If any further consideration or help needed, please feel free to post back.
Regards
Hi @jahida, @MattAllington and @v-micsh-msft
My Problem is: Getting Running Totals as 100% For All Three Calculations
What did i miss?
I have Fact with 'Itemkey',Yearkey,GrossSales columns, joined with 'DimItem' table on 'Itemkey'.
One ItemNumber in DimItem can have one or more ItemKey.
Fact is Grouped on Itemkey. Now
TotalGS = CALCULATE(SUM(GrossSale6s),ALL(DimItem[ItemNumber]))
GSContribution = DIVIDE(sum(GrossSales),[TotalGS],0)
RunninTotal(GSContribution) = MAXX(Fact,CALCULATE([GSContributio], Fact[GrossSales] >= EARLIEAR(Fact[GrossSales]),ALL(FACT)))
OR
RunninTotal(GSContribution) = CALCULATE([GSContribution],FILTER(Fact,Fact[GSContribution] >= MINX(Fact,[GSContribution])))
OR
RunninTotal(GSContribution) = MAXX(SELECTCOLUMNS(Fact,"Contr",[GSContribution]),CALCULATE([GSContribution],FILTER(Fact,[GSContribution] >= [Contr])))
Table viz:
ItemNumber(DimItem) GrossSales GSContribution RunningTotal(GSContribution)
1 500 45.45% 100%
2 300 27.27% 100%
3 200 18.18% 100%
N 100 9.09% 100%
Total 1100 100% 100%
Please help me with this.
I am very new to dax and Power BI.
taumirza,
Apologize for the late response.
Response to your earlier posts,
RunninTotal(GSContribution) = MAXX(Fact,CALCULATE([GSContributio], Fact[GrossSales] >= EARLIEAR(Fact[GrossSales]),ALL(FACT)))
OR
RunninTotal(GSContribution) = CALCULATE([GSContribution],FILTER(Fact,Fact[GSContribution] >= MINX(Fact,[GSContribution])))
OR
RunninTotal(GSContribution) = MAXX(SELECTCOLUMNS(Fact,"Contr",[GSContribution]),CALCULATE([GSContribution],FILTER(Fact,[GSContribution] >= [Contr])))
The first formula won't work, it will always return 100%, Second one should work, based on Descending order of [GSContribution], for the 3rd one, could you please explain a bit for the [Contr] here?
By the way, could you please share about your data view under Power BI Desktop screenshot, with the table column information that described here? We may provide a more suitable solution based on the DataSet.
Regards
Hi @v-micsh-msft,
all of the three fomulas giving me 100% only. Leave about that. consider this:
I have,
DimItem(ItemKey,ItemNumber,CompanyKey) (One ItemNumber can have Multiple ItemKey because of CompanyKey in it)
FactRevenue(ItemKey,CompanyKey,CustomerKey,BrandKey,DateKey,Date,Revenue) (Fact is day level)
Fact and Dim Item Joined on ItemKey.
when i try to put ItemNumber from DimItem and Revenue from FactRevenue in a table visual with following Measures.
TotalRevenue = CALCULATE(SUM(FactRevenue[Revenue]),ALL(DimItem[ItemNumber]))
RContr = DIVIDE(SUM(FactRevenue[Revenue]),[TotalRevenue],0)
1. RT = CALCULATE([RContr],FILTER(ALL(FactRevenue),FactRevenue[AccountingDate] <= MAX(FactRevenue[AccountingDate])),VALUES(DimItem[ItemNumber]))
OR
2. RT= MAXX(FactRevenue,CALCULATE([RContr],FactRevenue[Revenue] >= EARLIER(FactRevenue[Revenue]),ALL(FactRevenue)))
I am not getting good results. Please consider the output
1. RT
:
2. RT
I think all of those formulas assume that each line in the table corresponds to one line in the dataset, which in retrospect probably is unreasonable. I made a new formula that doesn't assume one entry per item number. I followed your naming for the table names, but not all the column names.
First a small change to TotalGS:
TotalGS = CALCULATE(SUM(Fact[Gross Sales]), ALL('DimItem'[Item]), ALLSELECTED(DimItem))
Then the main measure:
Run = SUMX(FILTER(CALCULATETABLE(SUMMARIZE('Fact', 'DimItem'[Item], "Contr", [GSContribution]), ALLSELECTED('DimItem'[Item])), [Contr] >= MINX(VALUES('DimItem'[Item]), [GSContribution])), [Contr])
If that doesn't work, please post a screenshot of the back-end data (top couple rows of the data table) so that we can know exactly what a solution would look like.
Hi @jahida,
I got this error when i use the formula you gave;
Error Message:
MdxScript(Model) (1, 199) Calculation error in measure 'PYQueryActual'[GS_RunningTotalPY]: Min or Max of booleans or strings are not currently supported.
Stack Trace:
Invocation Stack Trace:
Activity ID
23b334aa-8351-dda4-5704-77193dcda03e
Time
Mon Aug 15 2016 16:41:04 GMT+0530 (India Standard Time)
Version
2.37.4464.361 (PBIDesktop)
Error Code
rsDataShapeProcessingError
OData Error Message
Failed to execute the query.
Paste in the exact formula you tried..?
sorry @jahida,
i typed it wrong now there is no errors with formula but Running totals increased from 100% to 100.05% for every item in the Table.
here is what i did, as you said
First i chnged,
TotalGS = CALCULATE(SUM('Fact'[Gross Sales CY]),ALL(DimItem[ItemNumber]),ALLSELECTED(DimItem))
and then
RunningTotal = SUMX(FILTER(CALCULATETABLE(SUMMARIZE('Fact',DimItem[ItemNumber],"Contr",[GSContribution]),ALLSELECTED(DimItem[ItemNumber])),[Contr] >= MINX(VALUES(DimItem[ItemNumber]),[GSContribution])),[Contr])
Note: ItemNumber is different column and ItemKey is different column in DimItem
I have Itemkey in both Fact and DimItem on which i have joined.
ItemKey is Unique and ItemNumber can repeat
DimItem Ex: ItemKey ItemNumber Company
1 101 A
2 101 B
3 101 C
Does this fact have any effect on the formula
Please help me. Try to look at the image i am getting 100.05% now
Anybody there...!!!
Can't test because I don't know exactly what your data looks like, but here's my idea of a solution:
Running Total = MAXX(SELECTCOLUMNS(Query1, "Contr", [Contribution]), CALCULATE([Contribution], [Contribution] >= [Contr]))
or
Running Total2 = CALCULATE([Contribution], FILTER(Query1, [Contribution] >= MINX(Query1, [Contribution]))
Not sure if either of those will work but both probably worth a try. If not, you might want to take a screenshot of your back-end table so we can get an idea of exactly what your data looks like.
no @MattAllington,
it didn't workout for my sample data.
please consider the following screenshot.
All Measures
Here
1. Contr = DIVIDE(SUM(Query1[Revenue]),[Total Revenue],0)
where Total Revenue = CALCULATE(SUM(Query1[Revenue]),ALL(Query1))
and the formula you suggested
RT = CALCULATE([Contr],FILTER(ALL(Query1),Query1[ItemNumber] <= MAX(Query1[ItemNumber])))
2.. Contribution = SQL generated (Revenue / sum(Revenue) ) from SQL Source
Running Total = MAXX(Query1,CALCULATE(SUM(Query1[Contribution]),Query1[Contribution] >= EARLIER(Query1[Contribution]),ALL(Query1)))
in which 2 is working fine .But i dont want to calculate Contribution at SQL level.
User | Count |
---|---|
304 | |
148 | |
60 | |
57 | |
42 |
User | Count |
---|---|
363 | |
229 | |
96 | |
68 | |
61 |