Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have a data similar to this one.
ID | Desc | Group |
6628 | ASSY,CHAS,L6,7 | Chassis |
00J0 | ASSY,CHAS,L6,S | Chassis |
09FT | ASSY,CHAS,L6,5 | Chassis |
0CHJ | ASSY,CHAS,VDT, | Chassis |
0GD2 | ASSY,CHAS,7760 | Chassis |
0PD2 | ASSY,FSD,MSDIG | Internal Storage |
0VCY | ASSY,CHAS,2.5, | Chassis |
0XWG | ASSY,CHAS,2.5X | Chassis |
11R6 | ASSY,CHAS,VDT, | Chassis |
1P0X | ASSY,CHAS,L6,S | Chassis |
1R3T | ASSY,CHAS,L6,3 | Chassis |
1VRG | RDIMM | Memory |
1W50 | ASSY,CHAS,4X3. | Chassis |
1Y0N | ASSY,CHAS,3.5X | Chassis |
26KF | ASSY,CHAS,2.5X | Chassis |
and so on.
I have this line of code for calculative cumulative percentage from top.
Cumulative % of Total = var __visibleConfigs = ALL('Config Table'[PPID]) var __totalQtyForVisibleConfigs = CALCULATE( [Quantity Produced], __visibleConfigs ) var __currentQty = [Quantity Produced] var __visibleConfigsWithGreaterOrEqualQty = FILTER( __visibleConfigs, [Quantity Produced] >= __currentQty ) var __cumulativeQty = CALCULATE( [Quantity Produced], __visibleConfigsWithGreaterOrEqualQty ) var __cumulativeQtyPercent = if ( ISEMPTY( __visibleConfigsWithGreaterOrEqualQty ), 1, DIVIDE( __cumulativeQty, __totalQtyForVisibleConfigs ) ) return __cumulativeQtyPercent
[Quantity Produced] = COUNTDISTINCT(Archive[Product ID]).
This thing works good on top of other filters which is fine for me.
But, I need another thing.
The cumulative percentage should be group specific however the filter on the group may be.
The cumulative total of least [Quantity Produced] commodity should be 100% for a particular group. Now considering the entire list, the value is not 100%.
Any suggestion to small correction in this code would be highly helpful.
Second requirement, a seperate measure is requested to display code instead of cumulative percentage based on its value. Say, x<80% as F, 80<x<95 as M & 95<x as R. This should also as requested earlier, to happen according to the group.
Thanks
[Character Code] = // Bear in mind that percentages should always // be calculated as numbers in between 0 and 1. // If you want to show them as percentages, for // instance 51%, then you should format the number // as percentage instead of multiplying it by 100. var __cummulPercent = [Cumulative % of Total] var __code = switch( true(), __cummulPercent > .95, "R", __cummulPercent > .80, "M", "F" ) return __code
Best
Darek
-- I can't remember the whole setup, so I'm guessing -- here a bit. Check if this works. If not, it's because -- I can't see the model. Cumulative % of Total = // first, you have to know which groups are visible var __visibleGroups = VALUES ( 'Config Table'[Group] ) // then you have to retrieve all PPID's for the visible // groups, the rest goes as before var __visibleConfigs = calculatetable( values ( 'Config Table'[PPID] ), __visibleGroups, all ( 'Config Table' ) ) var __totalQtyForVisibleConfigs = CALCULATE( [Quantity Produced], __visibleConfigs ) var __currentQty = [Quantity Produced] var __visibleConfigsWithGreaterOrEqualQty = FILTER( __visibleConfigs, [Quantity Produced] >= __currentQty ) var __cumulativeQty = CALCULATE( [Quantity Produced], __visibleConfigsWithGreaterOrEqualQty ) var __cumulativeQtyPercent = if ( ISEMPTY( __visibleConfigsWithGreaterOrEqualQty ), 1, DIVIDE( __cumulativeQty, __totalQtyForVisibleConfigs ) ) return __cumulativeQtyPercent
Dear @Anonymous ,
You are marvellous.
The code worked. But, there is a small issue.
When I add the measure to one of the table, the table entries gets added with all the ID irrespective of the scenario in which the table is in.
For ex. I had a table visualisation that list only one group value for a particular day. It had only 12 IDs with some other measures in it. When I add this new FMR code to it, the table is filled with all the IDs and the corresponding FMR value. This is not intended. Where would be the error might be??
Whats your thought?
Thanks
Mate, since I can't see anything, I can't help you much. If the thing was working correctly before (apart from not being aware of groups), then you have to adjust this piece of code:
// first, you have to know which groups are visible var __visibleGroups = VALUES ( 'Config Table'[Group] ) // then you have to retrieve all PPID's for the visible // groups, the rest goes as before var __visibleConfigs = calculatetable( values ( 'Config Table'[PPID] ), __visibleGroups, all ( 'Config Table' ) )
because this is the only change. I don't know... Try to remove all ( 'Config Table' ) and see what happens.
Once again, I can't do anything because I can't see the model and understand the requirement. If you could paste some pics, that would certainly help.
Best
Darek
Hi @Anonymous ,
Here is the situation I am facing explained in detail.
Both the measures works fine as below.
You can see the above image that both the new measures works in the right and expected way.
Here (below) I have my scenario, where there are date, tray size filters & commodity filter(applied on visual) and unit status filter (applied on visual). This is fine.
But, when the FMR measure is introdued in the table, all the rows despite filters gets piles up.
The expected result would only be the code for the available rows.
Hope the condition is explained in the proper manner.
Much appreciation and thanks in advance for the solution and requesting a workaround to solve.
Thanks
@Anonymous , Kindly help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |