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!
Does anyone know how to divide running total value in a row by a TOTAL of a row?
My example:
Each row represents amount of shops someone has in the neighbourhood, and columns represent amount of shops visited.
E.g. 74 people have 2 shops in their neighbourhood. From which 74 people visited at least 1 shop and 50 people visited both shops.
I would like to get the fillowing result:
In order to calculate running total for each visited shop, I used the following measure:
Count of PersonId running total in VisitedShops = CALCULATE( DISTINCTCOUNT('MyData'[PersonId]), FILTER( ALLSELECTED('MyData'[VisitedShops]), ISONORAFTER('MyData'[VisitedShops], MIN('MyData'[VisistedShops]), ASC) ) )
I was trying to use the following measure but I didn't get the desired result:
MyResult = DISTINCTCOUNT('MyData'[PersonId])/ [Count of PersonID running total in VisitedShops]
Can anyone help me please?
Thank you!
Solved! Go to Solution.
Hi @juliausha ,
MyResult = [Count of PersonID running total in VisitedShops] / CALCULATE ( DISTINCTCOUNT ( MyData[PersonId] ), ALL ( MyData[VisistedShops] ) )
Best regards,
Yuliana Gu
What does your source data look like? Can you paste as text?
Hi Greg,
Here's how my data looks like:
PersonId,shops,VisistedShops Person1,1,1 Person2,1,1 Person3,1,1 Person4,1,1 Person5,1,1 Person6,1,1 Person7,1,1 Person8,1,1 Person9,1,1 Person10,1,1 Person11,1,1 Person12,1,1 Person13,1,1 Person14,1,1 Person15,1,1 Person16,1,1 Person17,1,1 Person18,1,1 Person19,1,1 Person20,1,1 Person21,1,1 Person22,1,1 Person23,1,1 Person24,1,1 Person25,1,1 Person26,1,1 Person27,1,1 Person28,1,1 Person29,1,1 Person30,1,1 Person31,1,1 Person32,1,1 Person33,1,1 Person34,1,1 Person35,1,1 Person36,1,1 Person37,1,1 Person38,1,1 Person39,1,1 Person40,1,1 Person41,1,1 Person42,1,1 Person43,1,1 Person44,1,1 Person45,1,1 Person46,1,1 Person47,1,1 Person48,1,1 Person49,1,1 Person50,1,1 Person51,1,1 Person52,1,1 Person53,1,1 Person54,1,1 Person55,1,1 Person56,1,1 Person57,1,1 Person58,1,1 Person59,1,1 Person60,1,1 Person61,1,1 Person62,1,1 Person63,1,1 Person64,1,1 Person65,1,1 Person66,1,1 Person67,1,1 Person68,1,1 Person69,1,1 Person70,1,1 Person71,1,1 Person72,1,1 Person73,1,1 Person74,1,1 Person75,1,1 Person76,1,1 Person77,1,1 Person78,1,1 Person79,1,1 Person80,1,1 Person81,1,1 Person82,1,1 Person83,1,1 Person84,1,1 Person85,1,1 Person86,1,1 Person87,1,1 Person88,1,1 Person89,1,1 Person90,1,1 Person91,1,1 Person92,1,1 Person93,1,1 Person94,1,1 Person95,1,1 Person96,1,1 Person97,1,1 Person98,1,1 Person99,1,1 Person100,1,1 Person101,1,1 Person102,1,1 Person103,1,1 Person104,1,1 Person105,1,1 Person106,1,1 Person107,1,1 Person108,1,1 Person109,1,1 Person110,1,1 Person111,1,1 Person112,1,1 Person113,1,1 Person114,1,1 Person115,1,1 Person116,1,1 Person117,1,1 Person118,1,1 Person119,1,1 Person120,1,1 Person121,1,1 Person122,1,1 Person123,1,1 Person124,1,1 Person125,1,1 Person126,1,1 Person127,1,1 Person128,1,1 Person129,1,1 Person130,1,1 Person131,1,1 Person132,1,1 Person133,1,1 Person134,1,1 Person135,2,1 Person136,2,1 Person137,2,1 Person138,2,1 Person139,2,1 Person140,2,1 Person141,2,1 Person142,2,1 Person143,2,1 Person144,2,1 Person145,2,1 Person146,2,1 Person147,2,1 Person148,2,1 Person149,2,1 Person150,2,1 Person151,2,1 Person152,2,1 Person153,2,1 Person154,2,1 Person155,2,1 Person156,2,1 Person157,2,1 Person158,2,1 Person159,2,2 Person160,2,2 Person161,2,2 Person162,2,2 Person163,2,2 Person164,2,2 Person165,2,2 Person166,2,2 Person167,2,2 Person168,2,2 Person169,2,2 Person170,2,2 Person171,2,2 Person172,2,2 Person173,2,2 Person174,2,2 Person175,2,2 Person176,2,2 Person177,2,2 Person178,2,2 Person179,2,2 Person180,2,2 Person181,2,2 Person182,2,2 Person183,2,2 Person184,2,2 Person185,2,2 Person186,2,2 Person187,2,2 Person188,2,2 Person189,2,2 Person190,2,2 Person191,2,2 Person192,2,2 Person193,2,2 Person194,2,2 Person195,2,2 Person196,2,2 Person197,2,2 Person198,2,2 Person199,2,2 Person200,2,2 Person201,2,2 Person202,2,2 Person203,2,2 Person204,2,2 Person205,2,2 Person206,2,2 Person207,2,2 Person208,2,2 Person209,3,1 Person210,3,1 Person211,3,1 Person212,3,1 Person213,3,1 Person214,3,1 Person215,3,1 Person216,3,1 Person217,3,1 Person218,3,1 Person219,3,2 Person220,3,2 Person221,3,2 Person222,3,2 Person223,3,2 Person224,3,2 Person225,3,2 Person226,3,2 Person227,3,2 Person228,3,2 Person229,3,2 Person230,3,2 Person231,3,3 Person232,3,3 Person233,3,3 Person234,3,3 Person235,3,3 Person236,3,3 Person237,3,3 Person238,3,3 Person239,3,3 Person240,3,3 Person241,3,3 Person242,3,3 Person243,3,3 Person244,3,3 Person245,3,3 Person246,3,3 Person247,3,3 Person248,3,3 Person249,3,3 Person250,4,1 Person251,4,1 Person252,4,2 Person253,4,2 Person254,4,2 Person255,4,2 Person256,4,2 Person257,4,2 Person258,4,2 Person259,4,2 Person260,4,3 Person261,4,4 Person262,4,4 Person263,4,4 Person264,4,4 Person265,4,4 Person266,5,1 Person267,5,1 Person268,5,1 Person269,5,1 Person270,5,1 Person271,5,1 Person272,5,1 Person273,5,1 Person274,5,1 Person275,5,1 Person276,5,1 Person277,5,2 Person278,5,2 Person279,5,3 Person280,5,3 Person281,5,4 Person282,5,4 Person283,5,4 Person284,5,4 Person285,5,5 Person286,5,5 Person287,5,5 Person288,5,5 Person289,5,5 Person290,5,5
Hope you are able to help.
Thanks!
Hi @juliausha ,
MyResult = [Count of PersonID running total in VisitedShops] / CALCULATE ( DISTINCTCOUNT ( MyData[PersonId] ), ALL ( MyData[VisistedShops] ) )
Best regards,
Yuliana Gu
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |