Forum Discussion
New Report
Hello!
I started a new job to help with sales using Power BI. Well I'm not a Power BI expert, I know the basics.
The problem is that I'm trying to understand how the report was created (the logic behind it), and there are at least 30 measures with names that don't help to understand why they were created nor comments to facilitate. I'm having trouble understanding each one, mainly because they are interconnected (the measures).
Any ideas or advice on how to better understand this report?
Here's an exemple:
This measure % ∆ was created using another two other measures:
% ∆ = DIVIDE([Cnpjs IDs/Qty],[Total Resellers],0)
So here´s the Cnpjs IDs/Qty:
Cnpjs IDs/Qty = SWITCH
(SELECTEDVALUE(Formato[Formato]),
"Qty",[CNPJ compraram com ID],
"Volume",[Total_Vol_ID´s])
And Total Resellers:
Hi Anonymous ,
SWITCH function is like a shorthand IF. It is easier to read than having many IF STATEMENTS.Cnpjs IDs/Qty = SWITCH ( SELECTEDVALUE ( Formato[Formato] ), "Qty", [CNPJ compraram com ID], "Volume", [Total_Vol_ID´s] )The above formula tells that if the value selected from Formato column in the Formato table is Qty then it will return the measure [CNPJ compraram com ID] and if volume then [Total_Vol_ID´s]
Total Resellers = SWITCH ( SELECTEDVALUE ( Formato[Formato] ), "Qty", DISTINCTCOUNT ( ChannelDnaReport[POSSoldToCustomerTaxIdRoot] ), "Volume", SUM ( ChannelDnaReport[SellThruProductUnits] ) )The above formula is very similar to the previous one except that instead of refrencing a measure when a value is selected from Formato[Formato], an aggregate expression is used - DISTINCTCOUNT(ChannelDnaReport[POSSoldToCustomerTaxIdRoot]) or SUM(ChannelDnaReport[SellThruProductUnits])
Both formulas will return blank/null if no value is selected from Formato[Formato] unless another condition is added to them to return otherwise.
2 Replies
Hi Anonymous ,
SWITCH function is like a shorthand IF. It is easier to read than having many IF STATEMENTS.Cnpjs IDs/Qty = SWITCH ( SELECTEDVALUE ( Formato[Formato] ), "Qty", [CNPJ compraram com ID], "Volume", [Total_Vol_ID´s] )The above formula tells that if the value selected from Formato column in the Formato table is Qty then it will return the measure [CNPJ compraram com ID] and if volume then [Total_Vol_ID´s]
Total Resellers = SWITCH ( SELECTEDVALUE ( Formato[Formato] ), "Qty", DISTINCTCOUNT ( ChannelDnaReport[POSSoldToCustomerTaxIdRoot] ), "Volume", SUM ( ChannelDnaReport[SellThruProductUnits] ) )The above formula is very similar to the previous one except that instead of refrencing a measure when a value is selected from Formato[Formato], an aggregate expression is used - DISTINCTCOUNT(ChannelDnaReport[POSSoldToCustomerTaxIdRoot]) or SUM(ChannelDnaReport[SellThruProductUnits])
Both formulas will return blank/null if no value is selected from Formato[Formato] unless another condition is added to them to return otherwise.