Need a help at Gross Margin % as a row values

putturps · June 3, 2020, 5:39pm

Hi DAX Experts,

I need some help with my below requirements.

I need the output in the above format, however, I was able to get for Revenu and Gross Margin, bu Gross Margin% I didn’t. since the values, I need to show at row level and I am not getting an idea of how to present it.

Any help much appreciated. Below is the data for the same.

FiscalYearQuarter	Category	Name	Value
FY2019-Q1	HD	REVENUE	0
FY2019-Q2	HD	REVENUE	0
FY2019-Q4	HD	REVENUE	0
FY2019-Q2	HD	REVENUE	0
FY2019-Q3	HD	REVENUE	0
FY2019-Q3	HD	REVENUE	0
FY2019-Q1	HD	REVENUE	0
FY2019-Q4	HD	REVENUE	0
FY2019-Q4	HD	REVENUE	0
FY2020-Q1	HD	REVENUE	0
FY2019-Q2	HD	REVENUE	0
FY2019-Q1	HD	REVENUE	0
FY2019-Q3	HD	REVENUE	0
FY2019-Q1	HD	COS	0
FY2019-Q2	HD	COS	0
FY2019-Q4	HD	COS	0
FY2019-Q2	HD	COS	0
FY2019-Q3	HD	COS	0
FY2019-Q3	HD	COS	0
FY2019-Q1	HD	COS	0
FY2019-Q4	HD	COS	0
FY2019-Q4	HD	COS	0
FY2020-Q1	HD	COS	0
FY2019-Q2	HD	COS	0
FY2019-Q1	HD	COS	0
FY2019-Q3	HD	COS	0
FY2019-Q1	HD	GROSS MARGIN	0
FY2019-Q2	HD	GROSS MARGIN	0
FY2019-Q4	HD	GROSS MARGIN	0
FY2019-Q2	HD	GROSS MARGIN	0
FY2019-Q3	HD	GROSS MARGIN	0
FY2019-Q3	HD	GROSS MARGIN	0
FY2019-Q1	HD	GROSS MARGIN	0
FY2019-Q4	HD	GROSS MARGIN	0
FY2019-Q4	HD	GROSS MARGIN	0
FY2020-Q1	HD	GROSS MARGIN	0
FY2019-Q2	HD	GROSS MARGIN	0
FY2019-Q1	HD	GROSS MARGIN	0
FY2019-Q3	HD	GROSS MARGIN	0
FY2019-Q1	SR	REVENUE	0
FY2019-Q1	SR	REVENUE	0
FY2019-Q1	SR	REVENUE	0
FY2019-Q2	SR	REVENUE	0
FY2019-Q2	SR	REVENUE	0
FY2019-Q2	SR	REVENUE	0
FY2019-Q3	SR	REVENUE	0
FY2019-Q3	SR	REVENUE	0
FY2019-Q3	SR	REVENUE	0
FY2019-Q4	SR	REVENUE	0
FY2019-Q4	SR	REVENUE	0
FY2019-Q4	SR	REVENUE	0
FY2019-Q1	SR	COS	0
FY2019-Q1	SR	COS	0
FY2019-Q1	SR	COS	0
FY2019-Q2	SR	COS	0
FY2019-Q2	SR	COS	0
FY2019-Q2	SR	COS	0
FY2019-Q3	SR	COS	0
FY2019-Q3	SR	COS	0
FY2019-Q3	SR	COS	0
FY2019-Q4	SR	COS	0
FY2019-Q4	SR	COS	0
FY2019-Q4	SR	COS	0
FY2019-Q1	SR	GROSS MARGIN	0
FY2019-Q1	SR	GROSS MARGIN	0
FY2019-Q1	SR	GROSS MARGIN	0
FY2019-Q2	SR	GROSS MARGIN	0
FY2019-Q2	SR	GROSS MARGIN	0
FY2019-Q2	SR	GROSS MARGIN	0
FY2019-Q3	SR	GROSS MARGIN	0
FY2019-Q3	SR	GROSS MARGIN	0
FY2019-Q3	SR	GROSS MARGIN	0
FY2019-Q4	SR	GROSS MARGIN	0
FY2019-Q4	SR	GROSS MARGIN	0
FY2019-Q4	SR	GROSS MARGIN	0
FY2019-Q3	HD	REVENUE	0
FY2019-Q3	HD	REVENUE	0
FY2019-Q1	HD	REVENUE	0
FY2019-Q2	HD	REVENUE	0
FY2019-Q4	HD	REVENUE	0
FY2020-Q1	HD	REVENUE	0
FY2019-Q3	HD	REVENUE	0
FY2020-Q1	HD	REVENUE	0
FY2019-Q2	HD	REVENUE	0
FY2019-Q1	HD	REVENUE	0
FY2019-Q4	HD	REVENUE	0
FY2019-Q4	HD	REVENUE	0
FY2019-Q1	HD	REVENUE	0
FY2019-Q2	HD	REVENUE	0
FY2019-Q4	HD	REVENUE	0
FY2019-Q3	HD	REVENUE	0
FY2019-Q1	HD	REVENUE	0
FY2019-Q2	HD	REVENUE	0
FY2019-Q3	HD	COS	0
FY2019-Q3	HD	COS	0
FY2019-Q1	HD	COS	0
FY2019-Q2	HD	COS	0
FY2019-Q4	HD	COS	0
FY2020-Q1	HD	COS	0
FY2019-Q3	HD	COS	0
FY2020-Q1	HD	COS	0
FY2019-Q2	HD	COS	0
FY2019-Q1	HD	COS	0
FY2019-Q4	HD	COS	0
FY2019-Q4	HD	COS	0
FY2019-Q1	HD	COS	0
FY2019-Q2	HD	COS	0
FY2019-Q4	HD	COS	0
FY2019-Q3	HD	COS	0
FY2019-Q1	HD	COS	0
FY2019-Q2	HD	COS	0
FY2019-Q3	HD	GROSS MARGIN	0
FY2019-Q3	HD	GROSS MARGIN	0
FY2019-Q1	HD	GROSS MARGIN	0
FY2019-Q2	HD	GROSS MARGIN	0
FY2019-Q4	HD	GROSS MARGIN	0
FY2020-Q1	HD	GROSS MARGIN	0
FY2019-Q3	HD	GROSS MARGIN	0
FY2020-Q1	HD	GROSS MARGIN	0
FY2019-Q2	HD	GROSS MARGIN	0
FY2019-Q1	HD	GROSS MARGIN	0
FY2019-Q4	HD	GROSS MARGIN	0
FY2019-Q4	HD	GROSS MARGIN	0
FY2019-Q1	HD	GROSS MARGIN	0
FY2019-Q2	HD	GROSS MARGIN	0
FY2019-Q4	HD	GROSS MARGIN	0
FY2019-Q3	HD	GROSS MARGIN	0
FY2019-Q1	HD	GROSS MARGIN	0
FY2019-Q2	HD	GROSS MARGIN	0
FY2020-Q1	HD	REVENUE	19248.17833
FY2020-Q1	HD	REVENUE	69511.24394
FY2020-Q1	HD	COS	21098.96421
FY2020-Q1	HD	COS	71634.98441
FY2020-Q1	HD	GROSS MARGIN	-1850.785879
FY2020-Q1	HD	GROSS MARGIN	-2123.740474
FY2019-Q1	SP	REVENUE	33.720539
FY2019-Q1	SP	REVENUE	11.062
FY2019-Q1	SP	REVENUE	57.285
FY2019-Q2	SP	REVENUE	41.815856
FY2019-Q2	SP	REVENUE	12.54936907
FY2019-Q2	SP	REVENUE	53.35
FY2019-Q3	SP	REVENUE	44.61768385
FY2019-Q3	SP	REVENUE	12.32861059
FY2019-Q3	SP	REVENUE	56.018
FY2019-Q4	SP	REVENUE	59.29851815
FY2019-Q4	SP	REVENUE	14.49
FY2019-Q4	SP	REVENUE	55.30147587
FY2019-Q1	SP	COS	40.27194985
FY2019-Q1	SP	COS	24.99068535
FY2019-Q1	SP	COS	65.22538534
FY2019-Q2	SP	COS	43.18846514
FY2019-Q2	SP	COS	24.04584454
FY2019-Q2	SP	COS	67.13616611
FY2019-Q3	SP	COS	38.76822477
FY2019-Q3	SP	COS	30.05502872
FY2019-Q3	SP	COS	63.22850634
FY2019-Q4	SP	COS	46.62591325
FY2019-Q4	SP	COS	29.27767149
FY2019-Q4	SP	COS	60.90160338
FY2019-Q1	SP	GROSS MARGIN	-6.551410852
FY2019-Q1	SP	GROSS MARGIN	-13.92868535
FY2019-Q1	SP	GROSS MARGIN	-7.94038534
FY2019-Q2	SP	GROSS MARGIN	-1.372609142
FY2019-Q2	SP	GROSS MARGIN	-11.49647547
FY2019-Q2	SP	GROSS MARGIN	-13.78616611
FY2019-Q3	SP	GROSS MARGIN	5.849459088
FY2019-Q3	SP	GROSS MARGIN	-17.72641814
FY2019-Q3	SP	GROSS MARGIN	-7.210506338
FY2019-Q4	SP	GROSS MARGIN	12.6726049
FY2019-Q4	SP	GROSS MARGIN	-14.78767149
FY2019-Q4	SP	GROSS MARGIN	-5.600127511
FY2019-Q1	SR	REVENUE	9263.22274
FY2019-Q1	SR	REVENUE	9856.85039
FY2019-Q1	SR	REVENUE	1044.40204
FY2019-Q1	SR	REVENUE	9961.07052
FY2019-Q1	SR	REVENUE	5090.99738
FY2019-Q1	SR	REVENUE	15397.62455
FY2019-Q2	SR	REVENUE	10617.54188
FY2019-Q2	SR	REVENUE	9650.35561
FY2019-Q2	SR	REVENUE	1318.04654
FY2019-Q2	SR	REVENUE	10354.88754
FY2019-Q2	SR	REVENUE	4964.76215
FY2019-Q2	SR	REVENUE	15106.91499
FY2019-Q3	SR	REVENUE	11657.17065
FY2019-Q3	SR	REVENUE	9442.33904
FY2019-Q3	SR	REVENUE	1569.00224
FY2019-Q3	SR	REVENUE	10371.73542
FY2019-Q3	SR	REVENUE	5280.13229
FY2019-Q3	SR	REVENUE	15550.49053
FY2019-Q4	SR	REVENUE	12471.57063
FY2019-Q4	SR	REVENUE	9592.67143
FY2019-Q4	SR	REVENUE	1303.76539
FY2019-Q4	SR	REVENUE	10372.79257
FY2019-Q4	SR	REVENUE	6774.93758
FY2019-Q4	SR	REVENUE	15489.45227
FY2019-Q1	SR	COS	6017.1622
FY2019-Q1	SR	COS	6012.29595
FY2019-Q1	SR	COS	652.98564
FY2019-Q1	SR	COS	7229.99749
FY2019-Q1	SR	COS	3155.3196
FY2019-Q1	SR	COS	12828.53586
FY2019-Q2	SR	COS	5932.85113
FY2019-Q2	SR	COS	6843.40647
FY2019-Q2	SR	COS	1024.37115
FY2019-Q2	SR	COS	6997.31602
FY2019-Q2	SR	COS	2537.19645
FY2019-Q2	SR	COS	12146.70244
FY2019-Q3	SR	COS	7001.2693
FY2019-Q3	SR	COS	6414.06903
FY2019-Q3	SR	COS	961.30466
FY2019-Q3	SR	COS	7150.13421
FY2019-Q3	SR	COS	3745.79191
FY2019-Q3	SR	COS	11804.54292
FY2019-Q4	SR	COS	9010.32772
FY2019-Q4	SR	COS	6383.59098
FY2019-Q4	SR	COS	816.67253
FY2019-Q4	SR	COS	5346.00202
FY2019-Q4	SR	COS	5023.22128
FY2019-Q4	SR	COS	11405.31612
FY2019-Q1	SR	GROSS MARGIN	3246.06054
FY2019-Q1	SR	GROSS MARGIN	3844.55444
FY2019-Q1	SR	GROSS MARGIN	391.4164
FY2019-Q1	SR	GROSS MARGIN	2731.07303
FY2019-Q1	SR	GROSS MARGIN	1935.67778
FY2019-Q1	SR	GROSS MARGIN	2569.08869
FY2019-Q2	SR	GROSS MARGIN	4684.69075
FY2019-Q2	SR	GROSS MARGIN	2806.94914
FY2019-Q2	SR	GROSS MARGIN	293.67539
FY2019-Q2	SR	GROSS MARGIN	3357.57152
FY2019-Q2	SR	GROSS MARGIN	2427.5657
FY2019-Q2	SR	GROSS MARGIN	2960.21255
FY2019-Q3	SR	GROSS MARGIN	4655.90135
FY2019-Q3	SR	GROSS MARGIN	3028.27001
FY2019-Q3	SR	GROSS MARGIN	607.69758
FY2019-Q3	SR	GROSS MARGIN	3221.60121
FY2019-Q3	SR	GROSS MARGIN	1534.34038
FY2019-Q3	SR	GROSS MARGIN	3745.94761
FY2019-Q4	SR	GROSS MARGIN	3461.24291
FY2019-Q4	SR	GROSS MARGIN	3209.08045
FY2019-Q4	SR	GROSS MARGIN	487.09286
FY2019-Q4	SR	GROSS MARGIN	5026.79055
FY2019-Q4	SR	GROSS MARGIN	1751.7163
FY2019-Q4	SR	GROSS MARGIN	4084.13615
FY2020-Q1	HD	REVENUE	5432.05919
FY2020-Q1	HD	REVENUE	2154.202289
FY2020-Q1	HD	COS	5554.495762
FY2020-Q1	HD	COS	1631.535421
FY2020-Q1	HD	GROSS MARGIN	-122.4365725
FY2020-Q1	HD	GROSS MARGIN	522.6668675
FY2019-Q1	SP	REVENUE	11.3657769
FY2019-Q1	SP	REVENUE	2.453986459
FY2019-Q1	SP	REVENUE	6.496337964
FY2019-Q2	SP	REVENUE	13.84321116
FY2019-Q2	SP	REVENUE	2.245091546
FY2019-Q2	SP	REVENUE	6.999021787
FY2019-Q3	SP	REVENUE	14.82758656
FY2019-Q3	SP	REVENUE	1.69932973
FY2019-Q3	SP	REVENUE	6.956795695
FY2019-Q4	SP	REVENUE	9.437665765
FY2019-Q4	SP	REVENUE	3.495261562
FY2019-Q4	SP	REVENUE	6.144359187
FY2019-Q1	SP	COS	7.868806204
FY2019-Q1	SP	COS	2.078006988
FY2019-Q1	SP	COS	6.626097207
FY2019-Q2	SP	COS	7.188971728
FY2019-Q2	SP	COS	2.624104479
FY2019-Q2	SP	COS	9.648194668
FY2019-Q3	SP	COS	4.2055031
FY2019-Q3	SP	COS	3.040754542
FY2019-Q3	SP	COS	9.31257846
FY2019-Q4	SP	COS	4.842250066
FY2019-Q4	SP	COS	2.654782786
FY2019-Q4	SP	COS	10.31740536
FY2019-Q1	SP	GROSS MARGIN	3.496970698
FY2019-Q1	SP	GROSS MARGIN	0.375979471
FY2019-Q1	SP	GROSS MARGIN	-0.129759242
FY2019-Q2	SP	GROSS MARGIN	6.654239432
FY2019-Q2	SP	GROSS MARGIN	-0.379012933
FY2019-Q2	SP	GROSS MARGIN	-2.649172882
FY2019-Q3	SP	GROSS MARGIN	10.62208346
FY2019-Q3	SP	GROSS MARGIN	-1.341424812
FY2019-Q3	SP	GROSS MARGIN	-2.355782766
FY2019-Q4	SP	GROSS MARGIN	4.595415699
FY2019-Q4	SP	GROSS MARGIN	0.840478776
FY2019-Q4	SP	GROSS MARGIN	-4.173046176
FY2019-Q1	SR	REVENUE	10835.59399
FY2019-Q1	SR	REVENUE	794.49843
FY2019-Q1	SR	REVENUE	3474.85491
FY2019-Q2	SR	REVENUE	13663.6139
FY2019-Q2	SR	REVENUE	1189.84543
FY2019-Q2	SR	REVENUE	3855.696346
FY2019-Q3	SR	REVENUE	11994.67191
FY2019-Q3	SR	REVENUE	1094.89801
FY2019-Q3	SR	REVENUE	2991.034013
FY2019-Q4	SR	REVENUE	9664.02951
FY2019-Q4	SR	REVENUE	1305.99961
FY2019-Q4	SR	REVENUE	3029.407594
FY2019-Q1	SR	COS	9831.362866
FY2019-Q1	SR	COS	657.3804676
FY2019-Q1	SR	COS	3042.408553
FY2019-Q2	SR	COS	14590.40711
FY2019-Q2	SR	COS	541.9574429
FY2019-Q2	SR	COS	3277.067815
FY2019-Q3	SR	COS	11486.6565
FY2019-Q3	SR	COS	627.6421366
FY2019-Q3	SR	COS	3319.802977
FY2019-Q4	SR	COS	8755.263219
FY2019-Q4	SR	COS	634.325473
FY2019-Q4	SR	COS	3097.971775
FY2019-Q1	SR	GROSS MARGIN	1004.231124
FY2019-Q1	SR	GROSS MARGIN	137.1179624
FY2019-Q1	SR	GROSS MARGIN	432.4463568
FY2019-Q2	SR	GROSS MARGIN	-926.7932055
FY2019-Q2	SR	GROSS MARGIN	647.8879871
FY2019-Q2	SR	GROSS MARGIN	578.6285319
FY2019-Q3	SR	GROSS MARGIN	508.015409
FY2019-Q3	SR	GROSS MARGIN	467.2558734
FY2019-Q3	SR	GROSS MARGIN	-328.7689646
FY2019-Q4	SR	GROSS MARGIN	908.7662912
FY2019-Q4	SR	GROSS MARGIN	671.674137
FY2019-Q4	SR	GROSS MARGIN	-68.56418156
FY2019-Q2	HD	REVENUE	8745.280744
FY2019-Q1	HD	REVENUE	19599.22501
FY2019-Q4	HD	REVENUE	10584.51665
FY2019-Q2	HD	REVENUE	30830.04751
FY2020-Q1	HD	REVENUE	34067.19005
FY2019-Q3	HD	REVENUE	10108.20481
FY2019-Q1	HD	REVENUE	9321.263262
FY2019-Q3	HD	REVENUE	32271.05184
FY2019-Q4	HD	REVENUE	31449.90529
FY2020-Q1	HD	REVENUE	8359.115519
FY2019-Q2	HD	COS	13254.26548
FY2019-Q1	HD	COS	20943.87754
FY2019-Q4	HD	COS	9699.818965
FY2019-Q2	HD	COS	35761.85172
FY2020-Q1	HD	COS	29197.70783
FY2019-Q3	HD	COS	8172.647643
FY2019-Q1	HD	COS	8360.328777
FY2019-Q3	HD	COS	33176.22269
FY2019-Q4	HD	COS	29850.78174
FY2020-Q1	HD	COS	8536.219427
FY2019-Q2	HD	GROSS MARGIN	-4508.984731
FY2019-Q1	HD	GROSS MARGIN	-1344.652532
FY2019-Q4	HD	GROSS MARGIN	884.6976812
FY2019-Q2	HD	GROSS MARGIN	-4931.804212
FY2020-Q1	HD	GROSS MARGIN	4869.482226
FY2019-Q3	HD	GROSS MARGIN	1935.55717
FY2019-Q1	HD	GROSS MARGIN	960.9344842
FY2019-Q3	HD	GROSS MARGIN	-905.1708502
FY2019-Q4	HD	GROSS MARGIN	1599.123554
FY2020-Q1	HD	GROSS MARGIN	-177.1039078
FY2019-Q1	SP	REVENUE	36.68074774
FY2019-Q1	SP	REVENUE	11.32294424
FY2019-Q1	SP	REVENUE	32.04021928
FY2019-Q2	SP	REVENUE	39.04192392
FY2019-Q2	SP	REVENUE	12.02960563
FY2019-Q2	SP	REVENUE	33.39814205
FY2019-Q3	SP	REVENUE	38.77003887
FY2019-Q3	SP	REVENUE	11.32907168
FY2019-Q3	SP	REVENUE	31.66243372
FY2019-Q4	SP	REVENUE	36.5718241
FY2019-Q4	SP	REVENUE	11.81155204
FY2019-Q4	SP	REVENUE	31.93485309
FY2019-Q1	SP	COS	17.86646133
FY2019-Q1	SP	COS	8.741370855
FY2019-Q1	SP	COS	13.69218137
FY2019-Q2	SP	COS	26.15605348
FY2019-Q2	SP	COS	8.360099902
FY2019-Q2	SP	COS	22.35064128
FY2019-Q3	SP	COS	25.40373706
FY2019-Q3	SP	COS	10.57632626
FY2019-Q3	SP	COS	19.61088938
FY2019-Q4	SP	COS	41.56259636
FY2019-Q4	SP	COS	10.44339143
FY2019-Q4	SP	COS	21.04447359
FY2019-Q1	SP	GROSS MARGIN	18.81428641
FY2019-Q1	SP	GROSS MARGIN	2.581573383
FY2019-Q1	SP	GROSS MARGIN	18.34803791
FY2019-Q2	SP	GROSS MARGIN	12.88587044
FY2019-Q2	SP	GROSS MARGIN	3.66950573
FY2019-Q2	SP	GROSS MARGIN	11.04750078
FY2019-Q3	SP	GROSS MARGIN	13.36630181
FY2019-Q3	SP	GROSS MARGIN	0.752745415
FY2019-Q3	SP	GROSS MARGIN	12.05154435
FY2019-Q4	SP	GROSS MARGIN	-4.990772253
FY2019-Q4	SP	GROSS MARGIN	1.368160616
FY2019-Q4	SP	GROSS MARGIN	10.8903795
FY2019-Q1	SR	REVENUE	64036.0175
FY2019-Q1	SR	REVENUE	14052.27735
FY2019-Q1	SR	REVENUE	40791.94893
FY2019-Q2	SR	REVENUE	73144.82287
FY2019-Q2	SR	REVENUE	14193.66268
FY2019-Q2	SR	REVENUE	41633.7085
FY2019-Q3	SR	REVENUE	72481.77283
FY2019-Q3	SR	REVENUE	15077.65191
FY2019-Q3	SR	REVENUE	42978.97382
FY2019-Q4	SR	REVENUE	74506.62894
FY2019-Q4	SR	REVENUE	15696.17554
FY2019-Q4	SR	REVENUE	41728.46084
FY2019-Q1	SR	COS	54502.50179
FY2019-Q1	SR	COS	11093.0108
FY2019-Q1	SR	COS	39841.81835
FY2019-Q2	SR	COS	55330.60921
FY2019-Q2	SR	COS	12445.63617
FY2019-Q2	SR	COS	40732.17136
FY2019-Q3	SR	COS	58232.25356
FY2019-Q3	SR	COS	11954.0253
FY2019-Q3	SR	COS	39127.40374
FY2019-Q4	SR	COS	59312.32481
FY2019-Q4	SR	COS	12412.0084
FY2019-Q4	SR	COS	40770.9859
FY2019-Q1	SR	GROSS MARGIN	9533.515711
FY2019-Q1	SR	GROSS MARGIN	2959.266548
FY2019-Q1	SR	GROSS MARGIN	950.1305832
FY2019-Q2	SR	GROSS MARGIN	17814.21366
FY2019-Q2	SR	GROSS MARGIN	1748.026513
FY2019-Q2	SR	GROSS MARGIN	901.5371481
FY2019-Q3	SR	GROSS MARGIN	14249.51927
FY2019-Q3	SR	GROSS MARGIN	3123.626607
FY2019-Q3	SR	GROSS MARGIN	3851.570075
FY2019-Q4	SR	GROSS MARGIN	15194.30413
FY2019-Q4	SR	GROSS MARGIN	3284.167143
FY2019-Q4	SR	GROSS MARGIN	957.4749316

EnterpriseDNA · June 4, 2020, 12:38am

Hi @putturps, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

skathuria · June 4, 2020, 9:09am

Hello @putturps,

Did you try creating the disconnected table that has a format like
Name Order
Revenue 1
Gross Margin 2
Gross Margin % 3

Then Creating the required measures of Revenue, GM, and GM%. Use The Name column in the table and matrix and in the value used below measure to get the switch values.

Measure = Switch(
                                 Min(Table[Order]),
                             "Revenue", [Revenue],
                             "Gross Margin", [GM],
                             "Gross Margin %", [GM%],
             Blank()
)

Replace [Revenue], [GM], and [GM%] with your created measures to get the desired values.

I hope this is what you were looking for and please mark this as solved if the measure works for you.

Thanks
Shubham

ankit · June 6, 2020, 2:40pm

Hi @putturps

Please check the attached Solution. Details below

Created a separate table “Display” with Values Revenue,Gross Margin, GM% along with Sort Column.
Created a Relationship b/w Display and Source Table

Created a Measure like below. This will return values of Gross Margin, Revenue as it is. For GM%,it is calculating based on values of GM and Revenue

 SumValue =
  
 VAR OtherValue =
     SUM ( Source[Value] )
 VAR RevValue =
     CALCULATE ( SUM ( Source[Value] ), DisplayTable[Item] = "Revenue",DisplayTable[ItemSort] = 1 )
 VAR GMValue =
     CALCULATE ( SUM ( Source[Value] ), DisplayTable[Item] = "Gross Margin",DisplayTable[ItemSort] = 2 )
 VAR GMPerValue =
     FORMAT ( DIVIDE ( GMValue, RevValue, 0 ), "Percent" )
 RETURN
     IF ( MAX ( DisplayTable[Item] ) = "GM%", GMPerValue, OtherValue )

PS: To understand the need for adding DisplayTable[ItemSort] = 1, go through the below Blog post.

Output is as below. Source Table has less rows, so values may show different.

PBIX file
EDNA_Solution_putturps.pbix (45 KB)

Let me know in case of any questions.

Thanks
Ankit J

BrianJ · June 6, 2020, 5:42pm

Great info – thanks for posting that. I’ve seen that unexpected result on ISFILTERED before, and didn’t know what was causing it.

Brian

samaguire · June 8, 2020, 1:03am

Yes, very good tip! Thanks for that.

EnterpriseDNA · June 9, 2020, 1:20am

Hi @putturps, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!