insert measures on a pivot

[Alberto95]

Hi, 

i would like to create a Pivot with some measures that i created in the model. 

the measures are not always the same because these change based on the selection of  the column "CodCommesssa". 

this is what i see in the simple table 

Valutazione_MDC_BI =

SWITCH(

    TRUE(),

    'MISURE'[Cumulative_MDC_Percent_BI] < 0.8, "A",

    'MISURE'[Cumulative_MDC_Percent_BI] <= 0.95, "B",

    "C"

)

Valutazione_Ric_BI =

SWITCH(

    TRUE(),

    'MISURE'[Cumulative_Ric_Percent_BI] < 0.8, "A",

    'MISURE'[Cumulative_Ric_Percent_BI] <= 0.95, "B",

    "C"

)

this is what i would like as a result of my pivot

(the first sub-row is the amount of "Ricavi Tot" and the second sub-row is the counting)

in many forum i read that i've to convet the measures to calculated column but i don't know do this. 

someone can help me? 

thanks

[Alberto95]

Thanks to all for replys but also Cumulative_MDC_Percent_BI is a Measure and i can't trasform it to column. 

there are 4 measure to create the last measures Valutazione_MDC_BI.

Values of the measures changes each time by the filter and if i created a calculated column, it wouldn't work well.

if you want you can find the pbix here to understand better how does it work:

https://www.dropbox.com/scl/fi/ciysddefdgvj3vobdhhuh/DASHBOARD-RATING-COMMESSE.pbix?rlkey=b0wkp70v4x...

[Anonymous]

Hi @Alberto95 ,

Thanks for the reply from mark_endicott .

Here is the example data I created to use for testing.

CodCommesssa	Ricavi Tot	Cumulative_MDC_Percent_BI	Cumulative_Ric_Percent_BI
001	1500	0.75	0.70
002	3000	0.90	0.95
003	4500	0.80	0.70
004	6000	0.70	0.92
005	7500	0.85	0.90
006	1200	0.65	0.70
007	3300	0.78	0.87
008	4700	1.22	1.22
009	6200	0.75	0.89
010	7600	0.88	1.22
011	1300	0.68	1.22
012	3600	0.77	0.86
013	4900	0.85	0.93
014	6100	0.72	0.88
015	7800	0.90	0.95

In power query, create two columns named Valutazione_MDC_BI, Valutazione_Ric_BI:

Perform a group by operation by these two new columns and calculate their sum Ricavi Tot and count.

Exit and save the data to use the matrix object in power bi desktop.

At this time sum and count are displayed side by side instead of above and below, if you want to display above and below, you can turn on the following option.

The final visual effect is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[mark_endicott]

@Anonymous - Thanks for pointing to the final step switching values to rows, I rarely use Matrix visuals so wasn't aware of this option!

[Alberto95]

Thanks to all for replys but also Cumulative_MDC_Percent_BI is a Measure and i can't trasform it to column. 

there are 4 measure to create the last measures Valutazione_MDC_BI.

Values of the measures changes each time by the filter and if i created a calculated column, it wouldn't work well.

if you want you can find the pbix here to understand better how does it work:

https://www.dropbox.com/scl/fi/ciysddefdgvj3vobdhhuh/DASHBOARD-RATING-COMMESSE.pbix?rlkey=b0wkp70v4x...

[mark_endicott]

@Alberto95 - I'd advise you to have a re-think of how to visualise this. Because what you are trying to do is possible in Power BI only with some very complex and inefficient workarounds. 

For example, this blog shows how you can use a measure as a custom label in a bar chart - to acheive the effect of an axis label: https://telefonicatech.uk/blog/pretty-power-bi-creating-dynamic-histograms/

But I couldnt guide you as to how this would be done in a table or matirx with out of the box functionality. 

Perhaps this could be done if you used SVG coding or with Deneb, but again this will require a lot of additional learning. 

[Alberto95]

it's not important how to visualise the results by table, matrix, card, chart,... .
it'd could be fine if i can visualise this results in different cards or in different tables but in this moment i can't.
i tried to filter a new measure but i i have to create for each group a different measure. 

[mark_endicott]

@Alberto95 - to create a calulated column, select the table in the "Data" tab and then select "New Column" from the toolbar. Paste your measure DAX into the new formula area that appears. 

You can then use a Matrix visual with both new columns in columns and rows, and your measures for the amount of "Ricavi Tot" and the counting (use COUNTROWS( 'Table' ) for this) in the Values section. 

The will appear side-by-side rather than above and below. But it is the closest you will get to you desired visual. 

If this helps, please accept as the solution so others with the same challenge can find it.