Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi everyone,
iam struggeling to make a cumulative percent calculation depent "Value" ( "Betrag" in German) for supplier.
I have made a percent calculation and it works well, but cumulative percent doesnt work.
Information about my Dataset:
I have many Supplier with value for each month and year.
For example one supplier:
So in my Dashboard i have a supplier table and looks like: (kumProzent -> cumulative Percent calculation) (Code is below)
So the result should be look like this: ( look last Picture, but it have a other Dataset(not mine) & the cumulative percent Value is right)
I thank you in advance and hope you can help me.
Kind regards,
Unknown
Solved! Go to Solution.
Hi @Anonymous ,
To create a calculated column as below.
Table2 = SUMMARIZECOLUMNS('Table'[Supplier],'Table',"value1",SUM('Table'[Value]),"perc",[percent],"id",MAX('Table'[SupplierID]))
Then add a calculated column in Table2.
rank = RANKX(ALL(Table2),Table2[perc],,DESC,Dense)
After that we can create a measure to get the excepted result as we need.
Measure 2 = CALCULATE(SUM(Table2[perc]),FILTER(ALLSELECTED(Table2),Table2[rank]<=MAX(Table2[rank])))
Regards,
Frank
Hi @Anonymous ,
One sample for your reference. If it doesn't meet your requirement, kindly share your sample data and excepted result to me. Please upload your files to One Drive and share the link here.
Here I create two measures.
Prozent = var sum1 = CALCULATE(SUM(Table1[betrag]),ALLSELECTED(Table1)) var sum2 = CALCULATE(SUM(Table1[betrag])) return DIVIDE(sum2,sum1)
Measure = CALCULATE ( SUMX ( FILTER ( ALL ( Table1[index] ), Table1[index] >= MAX ( Table1[index] ) ), [Prozent] ) )
Regards,
Frank
Hi @v-frfei-msft ,
thanks for you response and i have try your solution.
But unfortunatly it doesnt work, i think because i have multiple rows for suppliers. (look power bi file)
File (table snipped & data modified):
https://1drv.ms/u/s!Ar9P7_DTkCuW5Rd0x9MEaRS7Zj2v
And a additional question how to make a Rank with this power bi sample?
Because if i have only one row for each supplier with year, month, value thats easy. (your example)
But at the other hand with multiple same suppliers thats not easy and i get the classic error: "A single value for column 'Lieferant'(supplier) in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
Thanks thanks again for helping !!!
Kind regards,
Unknown
Hi @Anonymous ,
To create a calculated column as below.
Table2 = SUMMARIZECOLUMNS('Table'[Supplier],'Table',"value1",SUM('Table'[Value]),"perc",[percent],"id",MAX('Table'[SupplierID]))
Then add a calculated column in Table2.
rank = RANKX(ALL(Table2),Table2[perc],,DESC,Dense)
After that we can create a measure to get the excepted result as we need.
Measure 2 = CALCULATE(SUM(Table2[perc]),FILTER(ALLSELECTED(Table2),Table2[rank]<=MAX(Table2[rank])))
Regards,
Frank
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
80 | |
61 | |
60 | |
58 |
User | Count |
---|---|
155 | |
119 | |
103 | |
77 | |
71 |